Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    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>

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

  3. #3
    2 Star Lounger
    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!

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

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

  6. #6
    2 Star Lounger
    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!

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

  8. #8
    2 Star Lounger
    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>

  9. #9
    Platinum Lounger
    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.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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