Page 1 of 2 12 LastLast
Results 1 to 10 of 13

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

This is a discussion on SUMIF with blank cells criteria -- can it be done? within the Excel Questions forums, part of the Question Forums category; I'm trying to sum a range of cells (C1:C100) where the corresponding cells (A1:A100) are not blank. Unfortunately because of ...

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Sydney, Australia
    Posts
    80

    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
    Allentown, PA
    Posts
    2,547

    Default

    Try isnull
    ~Anne Troy

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Sydney, Australia
    Posts
    80

    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

    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
    Allentown, PA
    Posts
    2,547

    Default



    Way to go!
    ~Anne Troy

  6. #6
    New Member
    Join Date
    May 2010
    Posts
    1

    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
    294

    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

    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

    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

    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.

Page 1 of 2 12 LastLast

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
  •  


DMCA.com