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. 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. Try isnull

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

4. 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. Way to go!

6. 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. 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. Re: SUMIF with blank cells criteria -- can it be done?

Originally Posted by alvinwlh
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. 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. 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 Last

Posting Permissions

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