Nested sumif/sumproduct

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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
 
=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)
 
Thanks guys.
It appears to work just the way I needed.
Have a great day!
SteveB.

Ron Coderre said:
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


SteveDB1 said:
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?
Click to expand...
Click to expand...
 
Back
Top