Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Xray_Dave
 
Posts: n/a
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Peo Sjoblom
 
Posts: n/a
Default 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



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Scott Wagner
 
Posts: n/a
Default 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

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Xray_Dave
 
Posts: n/a
Default 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

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Xray_Dave
 
Posts: n/a
Default 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






  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
navychef
 
Posts: n/a
Default 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?
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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel Sum with multiple (over 2 criteria) Joe D Excel Discussion (Misc queries) 4 October 30th 05 01:35 PM
TRYING TO SET UP EXCEL SPREADSHEET ON MY COMPUTER MEGTOM New Users to Excel 5 October 27th 05 03:06 AM
Sumif - multiple criteria Anat Excel Discussion (Misc queries) 1 July 14th 05 03:01 AM
Array Formula w/ Multiple SumIf Criteria Andy Excel Worksheet Functions 3 July 13th 05 08:56 PM
Multiple Criteria for SUMIF camerons New Users to Excel 3 May 22nd 05 07:01 PM


All times are GMT +1. The time now is 11:51 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"