Does SUMIF in Excel, take multiple criteria input?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Xray_Dave - 13 Dec 2005 15:27 GMT

I am trying to SUM by month, what is spent on different products or buckets.
The formula I have been trying to make work is:
'=SUMIF(D10:E40,(AND(MONTH(1),G3)),F10:F40) where:

RANGE=D10:E40. Months are in Col D10:D40, and buckets are in E10:E40.
CRITERIA=(AND(MONTH(1),G3))
SUMRANGE=F10:F40
I have tried several others but don't seem to get even close.
Thanks
Dave

Reply to this Message

Peo Sjoblom - 13 Dec 2005 15:31 GMT

=SUMPRODUCT(--(ISNUMBER(D10:D40),--(MONTH(D10:D40)=1),--(E10:E40=G3),F10:F40
)

Signature

Regards,

Peo Sjoblom

> I am trying to SUM by month, what is spent on different products or buckets.
> The formula I have been trying to make work is:
[quoted text clipped - 6 lines]
> Thanks
> Dave

Reply to this Message

Xray_Dave - 13 Dec 2005 15:58 GMT

Peo Sjoblom,
Thanks for your reply, they were of great help.
Xray_Dave

> =SUMPRODUCT(--(ISNUMBER(D10:D40),--(MONTH(D10:D40)=1),--(E10:E40=G3),F10:F40
> )
[quoted text clipped - 10 lines]
> > Thanks
> > Dave

Reply to this Message

Scott Wagner - 13 Dec 2005 15:39 GMT

Take a look at these:
http://www.cpearson.com/excel/array.htm
http://www.contextures.com/xlFunctions01.html#SumProduct

> I am trying to SUM by month, what is spent on different products or buckets.
> The formula I have been trying to make work is:
[quoted text clipped - 6 lines]
> Thanks
> Dave

Reply to this Message

Xray_Dave - 13 Dec 2005 15:57 GMT

Scott Wagner,
Thanks for the references, they were right-on target.
Xray_Dave

> Take a look at these:
> http://www.cpearson.com/excel/array.htm
[quoted text clipped - 10 lines]
> > Thanks
> > Dave

Reply to this Message

navychef - 14 Dec 2005 21:32 GMT

I've also been playing with this, trying to use either IF, or SUMIF, or
SUMPRODUCT, trying to make this work (adding B+C, compare with D, and
determine if the sum is =, <, or >, than D, and giving the result in
E):

If B9+C9=D9, then 0
but if B9+C9>D9, then B9+C9-D9 (to show that "D" is x-amount less than
B+C)
but if B9+C9<D9, then D9-B9+C9 (to show that "D" is x-amount more than
B+C)

Am I making sense? :confused:
LOL - I'm sure I'm close, but I keep getting the "too many arguments"
error

THANK YOU :)

Signature

navychef

Reply to this Message