VBA
Como você usou a opção "format" para formatar essas células, a formatação não está contida no valor da célula, portanto, precisaremos definir uma função no VBA. Coloque isso ( fonte ) em um novo módulo:
Public Function sumFormats(rng As Range) As String
Application.Volatile
Dim cell As Range, dblDollar#, dblPound#
dblDollar = 0: dblPound = 0
For Each cell In rng
If Len(cell.Value) > 0 Then
If Left(cell.Text, 1) = "$" Then
dblDollar = dblDollar + cell.Value
Else
dblPound = dblPound + cell.Value
End If
End If
Next cell
sumFormats = "Sum of currency: $" & dblDollar & "; Sum of Pounds: " & ChrW(163) & dblPound
End Function
Em seguida, use a nova função =sumFormats(A1:A20) para as células que você quiser e você receberá as somas em uma linha. Se você precisar de subtotais em linhas diferentes, precisaremos modificar a saída da função.
Não-VBA
Se você não quiser mexer com o VBA, precisará de uma coluna auxiliar ao lado dos números. Nessa coluna, use a função =cell("format",A1) e isso retornará ,2 para £ e C2 para $. Em seguida, execute uma função =sumif para os subtotais.
=sumif(helpcolumncell,"C2",currencycell) para somar em dólares e substituir C2 por ,2 por libras. Você sempre pode ocultar a coluna de ajuda se não gostar dela. Nesse caso, assumiremos que seus dados estão na Coluna B e o ajudante é a Coluna C.
A B C
Data Format
$1 =cell("format",B2) = C2
£2 =cell("format",B3) = ,2
£3 =cell("format",B4) = ,2
$4 =cell("format",B5) = C2
sum dollar =sumif($C$2:$C$4,"C2",$B$2:$B$4) = 5
sum pound =sumif($C$2:$C$4,",2",$B$2:$B$4) = 5
Em seguida, você precisará formatar essas células para obter o sinal de moeda ou adicionar uma =concatenate("$"&sumif(...)) function