Sumif or sumproduct?

[BobTheRocker]

Column A contains Male or Female. Column B contains a number. I need a formula which will add up the numbers in column b that are male.

A B
Male 10
Female 8
Male 4
Male 8
Male 2
Female 11
Female 3

[DonkeyOte]

SUMIF.

=SUMIF(A:A,"Male",B:B)

[BobTheRocker]

Thank you that works, must do some serious reading on formula. Any books or resources you can recommend?

[DonkeyOte]

The Help Files aren't too bad... you might find the Excel "Dictionary" referenced in my signature useful as the accompanying file has working examples of many of the functions available.

I only own one book myself and that's because it was free... Excel Gurus Gone Wild ... but that focuses on the more obscure techniques.

[BobTheRocker]

Thank you very much for your help, your links are very good. One further question, is there a way to average the sum of this =SUMIF(A:A,"Male",B:B) i.e. the average if the total of b:b?

[DonkeyOte]

Pre XL2007 you have a couple of choices

1 - use SUMIF/COUNTIF

=SUMIF(A:A,"Male",B:B)/COUNTIF(A:A,"Male")

2 - use an Average Array

=AVERAGE(IF(A1:A100="Male",B1:B100))
confirmed with CTRL + SHIFT + ENTER

Note with option 2 the reduced range size - this is because Array formulae are inefficient and indeed pre XL2007 use of entire column references (A:A) is not permitted (#NUM error)

As a general rule of thumb, where possible, use SUMIF/COUNTIF in pref. to an Array
(Arrays should generally be used in moderation and as method of last resort)

[BobTheRocker]

Thank you Donkeyote but the problem I have is when a row in column B does not contain any data as it has not been filled in. This returns a false average. I need a way of dividing by the number of entries in column b that satisfy the Male criteria of column A.
So for the example below the formula should return 20(Total Male Scores)/3(The total male scores that have been entered.)

Hope this makes sense.


A B
Male 10
Female 8
Male
Male 8
Male 2
Female 11
Female 3

[NBVC]

Since you have 2 conditions, you need the 2nd choice:

e.g.


=AVERAGE(IF(A1:A100="Male",IF(B1:B100<>"",B1:B100)))

confirmed with CTRL+SHIFT+ENTER not just ENTER.

if you don't like the CSE option then

=SUMPRODUCT(--(A1:A100="Male"),--(B1:B100<>""),B1:B100)/SUMPRODUCT(--(A1:A100="Male"),--(B1:B100<>""))