Multiple Criteria in a SUMIF or COUNTIF function

  • Thread starter Thread starter Jack Gillis
  • Start date Start date
J

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.
 
=SUMIF(NAME,"aname" .AND. TYPE,"atype",AMOUNT)

Try:
=SUMPRODUCT((NAME="aname")*(TYPE="atype"),AMOUNT)
The 3 defined ranges must be identically sized
 
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.
 
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)
 
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.
 
Back
Top