Multiple Criteria in a SUMIF or COUNTIF function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jack Gillis - 28 Mar 2007 17:25 GMT

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.

Reply to this Message

Max - 28 Mar 2007 17:35 GMT

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

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

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

>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
[quoted text clipped - 9 lines]
>
> Thank you very much.

Reply to this Message

Jack Gillis - 28 Mar 2007 18:00 GMT

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.

>> =SUMIF(NAME,"aname" .AND. TYPE,"atype",AMOUNT)
>
[quoted text clipped - 14 lines]
>>
>> Thank you very much.

Reply to this Message

Bob Phillips - 28 Mar 2007 19:51 GMT

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?

Signature

HTH

Bob

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

> Thank you.
>
[quoted text clipped - 23 lines]
>>>
>>> Thank you very much.

Reply to this Message

Jack Gillis - 28 Mar 2007 20:59 GMT

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.

> The help on SUMPRODUCT is irrelevant, it doesn't cover the type of usage
> that Max gave you.
[quoted text clipped - 28 lines]
>>>>
>>>> Thank you very much.

Reply to this Message