Query SQL Server - Union

The point is that union really does have different rows, so you just have to group those results together into one :

SELECT Consultor, UsuID, SUM([Tentativa 1 Abertos])
FROM (
    select U.UsuNome Consultor, U.UsuID, (COUNT((S.SolID))) AS [Tentativa 1 Abertos]
    from Solicitacao S 
    left join Usuario U on U.UsuID = S.UsuIDResponsavel
    WHERE U.UsuTipo = 'A' AND U.UsuIDGrupo = 30 AND U.EmpLiberada = 1 and u.UsuUsuario is not null
    and S.SolEstagioID IN (235,276,278,294) AND S.SolStatus IN (0,1,5)-- AND S.UsuIDCliente not in (select Y.UsuIDCliente from Solicitacao Y where Y.SolTipID in (710) and Y.SolStatus in (0,1,5))
    GROUP BY U.UsuID, U.UsuNome

    union

    select U.UsuNome Consultor, U.UsuID, (COUNT((S.TarID))) AS [Tentativa 1 Abertos]
    from Tarefa S 
    left join Usuario U on U.UsuID = S.UsuIDResponsavel
    WHERE U.UsuTipo = 'A' AND U.UsuIDGrupo = 30 AND U.EmpLiberada = 1 and u.UsuUsuario is not null
    and S.TarEstagioID IN (235,276,278,294) AND S.TarStatus IN (0,1,5)-- AND S.UsuIDCliente not in (select Y.UsuIDCliente from Solicitacao Y where Y.SolTipID in (710) and Y.SolStatus in (0,1,5))
    GROUP BY U.UsuID, U.UsuNome
) as tabela
GROUP BY Consultor, UsuID

Try to make another select.

select Consultor, UsuID , sun([Tentativa 1 Abertos])
From
(
    select
    U.UsuNome Consultor, U.UsuID, (COUNT((S.SolID))) AS [Tentativa 1 Abertos]
    from Solicitacao S 
    left join Usuario U on U.UsuID = S.UsuIDResponsavel
    WHERE U.UsuTipo = 'A' AND U.UsuIDGrupo = 30 AND U.EmpLiberada = 1 and u.UsuUsuario is not null
    and S.SolEstagioID IN (235,276,278,294) AND S.SolStatus IN (0,1,5)-- AND S.UsuIDCliente not in (select Y.UsuIDCliente from Solicitacao Y where Y.SolTipID in (710) and Y.SolStatus in (0,1,5))
    GROUP BY U.UsuID, U.UsuNome

    union

    select
    U.UsuNome Consultor,
           U.UsuID, 
           (COUNT((S.TarID))) AS [Tentativa 1 Abertos]
    from Tarefa S 
    left join Usuario U on U.UsuID = S.UsuIDResponsavel
    WHERE U.UsuTipo = 'A' AND U.UsuIDGrupo = 30 AND U.EmpLiberada = 1 and u.UsuUsuario is not null
    and S.TarEstagioID IN (235,276,278,294) AND S.TarStatus IN (0,1,5)-- AND S.UsuIDCliente not in (select Y.UsuIDCliente from Solicitacao Y where Y.SolTipID in (710) and Y.SolStatus in (0,1,5))
    GROUP BY U.UsuID, U.UsuNome
)saida
GROUP BY saida.UsuID, saida.UsuNome