Sum if a cell is not a certain data

Jonno · Feb 10, 2010

Hi,

I have a set off data like this......

Jonno 3
Alex 5
Jonno 5
Simon 5
Simon 5
Alex 3

and i have a formula to calculate the total sum of jonno's (i.e 8)

but i would like it to calculate if its not jonno i.e simon and alex (18)

am thinking this will be possible but i cannot figure out how.

Thanks in advance,

Gary''s Student · Feb 10, 2010

=SUMIF(A1:A6,"<>jonno",B1:B5)

Bob I · Feb 10, 2010

total sum - sum of jonno's

Jim Thomlinson · Feb 10, 2010

A couple of possible solutions...
With SumIf
=SUM(B2:B10) - SUMIF(A2:A10, "=Jonno", B2:B10)
With SumProduct
=SUMPRODUCT(--(A2:A10<>"Jonno"), B2:B10)

Jonno · Feb 12, 2010

Thank you very much!!!
--
Jonno

Gary''s Student said:
=SUMIF(A1:A6,"<>jonno",B1:B5)

Jonno · Feb 12, 2010

Thank you very much!!!
--
Jonno

Jim Thomlinson said:
A couple of possible solutions...
With SumIf
=SUM(B2:B10) - SUMIF(A2:A10, "=Jonno", B2:B10)
With SumProduct
=SUMPRODUCT(--(A2:A10<>"Jonno"), B2:B10)

Jonno · Feb 12, 2010

Thank you very much!!!
--
Jonno

Bob I said:
total sum - sum of jonno's

.