Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: SUMIF with blank cells criteria -- can it be done?

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Sydney, Australia
    Posts
    80
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I'm trying to sum a range of cells (C1:C100) where the corresponding cells (A1:A100) are not blank. Unfortunately because of the format for the criteria which requires operators to be noted with quotation marks, I cannot use "" to represent blank cells. I have tried the formula
    =SUMIF(A1:A100,"<>0",C1:C100)
    but the zero-value is clearly not equal to an empty cell value, as it is not giving the correct answer unless I specifically change those empty cells to actual zeros.

    Any ideas?

  2. #2
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try isnull
    ~Anne Troy

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Sydney, Australia
    Posts
    80
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I'd tried ISBLANK and that didn't work. Unfortunately ISNULL doesn't quite do the job either...

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Sydney, Australia
    Posts
    80
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ah ha! Found it. I can use "<>" to do the job --
    SUMIF(A1:A100,"<>",C1:C100)

    I did a site search using Google, but I should have used Mr Excel's own message board search, and I would have found the answer here all along -- http://mrexcel.com/board/viewtopic.p...ic=111&forum=2

    Thanks for the assistance, though!

    --Jason--

  5. #5
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default



    Way to go!
    ~Anne Troy

  6. #6
    New Member
    Join Date
    May 2010
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUMIF with blank cells criteria -- can it be done?

    Just in case people are still looking at this thread.
    I tried the "<>" and it didn't work for me, so I reversed it "><" and that worked fine.

    Note: I'm using Excel 2000

  7. #7
    Board Regular
    Join Date
    Feb 2009
    Location
    Singapore
    Posts
    305
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUMIF with blank cells criteria -- can it be done?

    Hi,

    Try:
    Code:
    =SUM(IF(A1:A100<>"",C1:C100))
    Shift + Ctrl + Enter
    
    will be 
    {=SUM(IF(A1:A100<>"",C1:C100))}

  8. #8
    New Member
    Join Date
    Mar 2013
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUMIF with blank cells criteria -- can it be done?

    Quote Originally Posted by alvinwlh View Post
    Hi,

    Try:
    Code:
    =SUM(IF(A1:A100<>"",C1:C100))
    Shift + Ctrl + Enter
    
    will be 
    {=SUM(IF(A1:A100<>"",C1:C100))}
    Well, I use the Dutch version of Excel
    Meaning this matrix has to be =SOM(ALS(A1:A100="";C1:C100))
    Works great, but...
    When I use the "secure tab" (hope this is the right translation) is doesn't work
    So every time I have to stop secure, and restart it. Too bad!

  9. #9
    New Member
    Join Date
    Apr 2013
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUMIF with blank cells criteria -- can it be done?

    I need to test on a column that is custom format ##,##0.00, if it is 0, then, add up the cells in another column.

    1. So, I had =SUMIF(J23:J32,"=0",H23:H32) I got 0 instead of the right sum amount for the corresponding H23:H32 cells.

    Not sure why it happened?

    2. Then, I format J23:J32 to be number and tried again, I still got 0 as the sum.

    3. I created another column L with =IF(J23=0,0,1) then =SUMIF(L23:L32,"=0",H23:H32) This approach added up to the right total.

    Why would this work when approach #1 doesn't?

    Format doesn't seem to make any difference since I tried both.

  10. #10
    New Member
    Join Date
    Apr 2013
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUMIF with blank cells criteria -- can it be done?

    The value of the 0 cells =value(J23) is 0, even though it is blank in the cell.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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