average if sumproduct?

[excelnovice12345]

hi everyone,
im trying to average age (D:D) for individuals who are male (C:C) and in a certain group (B:B).
so far i isolated the males of a certain group by typing in
=SUMPRODUCT((C:C="male")*(B:B="case")) but now i am interested in averaging this isolated population's age.

i was thinking =AVERAGE(IF((SUMPRODUCT((C:C="male")*(B:B="case")),D:D) but it doesnt seem to work. what am i doing wrong here?

[DonkeyOte]

If using XL2007 as implied by profile (and use of entire column references) use AVERAGEIFS

Please Login or Register  to view this content.

avoid use of entire column references with SUMPRODUCT / Arrays at all costs (inefficient).

[NBVC]

Since you have 2007, try:

=AVERAGEIFS(D:D,C:C,"male",B:B,"case")

[excelnovice12345]

so if i wanted to look for things in columns A, B, C for values x, y, and z respectively, how would i formulate it?

[NBVC]

=AVERAGEIFS(D:D,A:A,"x",B:B,"y",C:C,"z")

where you are averaging column D based on those criteria.

Have a look at Averageifs help in Excel