Results 1 to 9 of 9
Thread: Sumif Criteria (2003)

20050609, 09:21 #1
 Join Date
 Jan 2001
 Location
 Fishburn, Durham, England
 Posts
 125
 Thanks
 0
 Thanked 0 Times in 0 Posts
Sumif Criteria (2003)
I am trying to sum a row of numbers if they are greater than a calculated cell. I can't get the formula to work. If I put a number in, it works but I can't make it refer to a cell. This works: sumif(c3:c337, "> 58"); these don't: sumif(c2:c337, ">$C$341"), SUMIF(C2:C337, ">" $C$341).
Can anyone tell me what I am doing wrong? This is driving me nuts. <img src=/S/nuts.gif border=0 alt=nuts width=15 height=15>

20050609, 09:56 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Sumif Criteria (2003)
You must concatenate the parts:
=SUMIF(C2:C337, ">"&$C$341)
The & concatenates the literal string ">" and the value of the cell C341.

20050609, 10:12 #3
 Join Date
 Jan 2001
 Location
 Fishburn, Durham, England
 Posts
 125
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Sumif Criteria (2003)
Clever, thank you!

20050609, 10:56 #4
 Join Date
 Jan 2001
 Location
 Fishburn, Durham, England
 Posts
 125
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Sumif Criteria (2003)
Actually, my problem is slightly more complicated as I need the comparison to look at the hour component. I have imported data that has date and time in the first column. I need to sum the second column if the second column falls within a certain time. I have tried to do it using the following formula, but it ends up summing everything as it is looking at the overall number which is obviously greater than just the hour element.
This is the formula I was trying: =(SUMIF(B2:B337, ">="&(HOUR($C$347)), C2:C337)SUMIF(B2:B337, "<"&(HOUR($C$348)), C2:C337))/2
Any suggestions?

20050609, 11:16 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Sumif Criteria (2003)
Here is a possible formula:
=SUMPRODUCT((HOUR(B2:B337)<=HOUR(C348))*C2:C337)SUMPRODUCT((HOUR(B2:B337)<=HOUR(C347))*C2:C337)
Or an array formula (confirm it with Ctrl+Shift+Enter instead of just Enter):
=SUM(IF(HOUR(B2:B337)<=HOUR(C348),C2:C337))SUM(IF(HOUR(B2:B337)<=HOUR(C347),C2:C337))
(I didn't include the /2, you can add that if necessary)

20050609, 11:26 #6
 Join Date
 Jan 2001
 Location
 Fishburn, Durham, England
 Posts
 125
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Sumif Criteria (2003)
Both work great, thanks. I wondered how sumproduct worked!

20050609, 11:31 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Sumif Criteria (2003)
Yet another one (as a 'normal' formula):
=SUMPRODUCT((HOUR(B2:B337)<=HOUR(C348))*(HOUR(B2:B 337)>=HOUR(C347))*C2:C337)
Another possibility would be to add an extra column to calculate the hour value of B2:B337: say that you enter =HOUR(B2) in D2 and fill down to D337; you could then use SUMIF formulas with D2337 instead of B2:B337.

20050609, 11:36 #8
 Join Date
 Jan 2001
 Location
 Fishburn, Durham, England
 Posts
 125
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Sumif Criteria (2003)
I thought about adding another column, but I wanted to do something a bit more impressive and "clever" looking! <img src=/S/sneaky.gif border=0 alt=sneaky width=15 height=15>

20050609, 12:06 #9
 Join Date
 Feb 2001
 Location
 Weert, Limburg, Netherlands
 Posts
 4,812
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Sumif Criteria (2003)
Array formulas are very nice and indeed clever looking. I use them a lot. Be aware though, that they are very calculation intensive and can greatly slow down a workbook. Using extra columns can help speed things up.
Even more efficient is to use Excel's database functions DSUM, DAVERAGE, ....Jan Karel Pieterse
Microsoft Excel MVP, WMVP
www.jkpads.com
Professional Office Developers Association