Nested sumif/sumproduct

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


"SteveDB1" wrote:

> 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?
>
>

=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" <[email protected]> wrote in message
news:[email protected]...
> 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?
>
>

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

"Ron Coderre" wrote:

> 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" wrote:
>
> > 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?
> >
> >