Multiple Criteria in a SUMIF or COUNTIF function

[Jack Gillis]

I have a worksheet with three ranges with range names Amount,Name and Type.
I would like to write a SUMIF formula to give me the total based on two
criteria. . Amount is numeric and Type and Name are text. I have tried
something like this but keep getting an error.

=SUMIF(NAME,"aname" .AND. TYPE,"atype",AMOUNT) Clearly this doesn't work.
A pivot table is not an option in this case for several reasons.


=SUMIF(NAME,"aname",AMOUNT) yields the proper result.

Can someone help me out here?

Thank you very much.

 

[Max]

=SUMIF(NAME,"aname" .AND. TYPE,"atype",AMOUNT)

Try:
=SUMPRODUCT((NAME="aname")*(TYPE="atype"),AMOUNT)
The 3 defined ranges must be identically sized

 

[Jack Gillis]

Thank you.

The three ranges are identical in size. However I get #NUM when I used
what you suggested. Perhaps that has to do with aname and atype being
non-numeric. According to Help, Sumproduct handles non-numerics as 0 but I
don't really know the effect of that.

Thanks again.

 

[Bob Phillips]

The help on SUMPRODUCT is irrelevant, it doesn't cover the type of usage
that Max gave you.

Are you sure that none of the AMOUNT range doesn't contain #NUM?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Jack Gillis]

Well now! It never dawned on me that 'Amount' included the column heading
Amount as field name in a database definition. I will fix that and see what
happens. Probably won't have a chance until tomorrow.

Thanks very much.