Nested sumif/sumproduct

[SteveDB1]

Hi all.
Ok, so sumif only allows one criteria test, where the item being tested
against is the second part of the sumif-
sumif(criteria-range, criteria-test,sum-range)

Sumproduct appears to only allow the multiplication of the criteria being
searched.

I want to look at two distinct, and different types of data (one is a
numeric value[say a file #], and the other a name[Eg, Dave]), compare both to
a specific value, and sum another column- with the corresponding rows.
I hope that's clear.
I.e.,
search criteria range A AND range B, test those values against two cells,
sum range C that are on the same row as my test.
Is this clear?

 

[Ron Coderre]

Try something like this:

=SUMPRODUCT((A1:A100="file #001")*(B1:B100="Dave")*C1:C100)

That formula sums all of the Col_C cells where the corresponding cells in
Col_A and Col_B cell match "file #001" and "Dave", respectively.

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Bob Phillips]

=SUMPRODUCT(--(A2:A200=number_cell),--(B2:b200=value_cell),C2:C200)

Note that SUMPRODUCT doesn't work with complete columns, you have to specify
a range.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[SteveDB1]

Thanks guys.
It appears to work just the way I needed.
Have a great day!
SteveB.

Try something like this:

=SUMPRODUCT((A1:A100="file #001")*(B1:B100="Dave")*C1:C100)

That formula sums all of the Col_C cells where the corresponding cells in
Col_A and Col_B cell match "file #001" and "Dave", respectively.

Does that help?
***********
Regards,
Ron

XL2002, WinXP

Hi all.
Ok, so sumif only allows one criteria test, where the item being tested
against is the second part of the sumif-
sumif(criteria-range, criteria-test,sum-range)

Sumproduct appears to only allow the multiplication of the criteria being
searched.

I want to look at two distinct, and different types of data (one is a
numeric value[say a file #], and the other a name[Eg, Dave]), compare both to
a specific value, and sum another column- with the corresponding rows.
I hope that's clear.
I.e.,
search criteria range A AND range B, test those values against two cells,
sum range C that are on the same row as my test.
Is this clear?