1. ## 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>

2. ## 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.

3. ## Re: Sumif Criteria (2003)

Clever, thank you!

4. ## 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?

5. ## 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)

6. ## Re: Sumif Criteria (2003)

Both work great, thanks. I wondered how sumproduct worked!

7. ## 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.

8. ## 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>

9. ## 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, ....

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•