Does SUMIF in Excel, take multiple criteria input?
 

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) whe

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

Does SUMIF in Excel, take multiple criteria input?
 

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

--

Regards,

Peo Sjoblom

"Xray_Dave" wrote in message
...
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) whe

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

Does SUMIF in Excel, take multiple criteria input?
 

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

"Xray_Dave" wrote:

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) whe

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

Does SUMIF in Excel, take multiple criteria input?
 

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

"Scott Wagner" wrote:

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

"Xray_Dave" wrote:

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) whe

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

Does SUMIF in Excel, take multiple criteria input?
 

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

"Peo Sjoblom" wrote:

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

--

Regards,

Peo Sjoblom

"Xray_Dave" wrote in message
...
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) whe

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

Does SUMIF in Excel, take multiple criteria input?
 

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+C9D9, 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 :)


--
navychef
------------------------------------------------------------------------
navychef's Profile: http://www.excelforum.com/member.php...o&userid=29457
View this thread: http://www.excelforum.com/showthread...hreadid=493042