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

jasonconlon

Board Regular
Joined
Mar 14, 2002
Messages
80
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?
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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
 
Upvote 0
Hi,

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

will be 
{=SUM(IF(A1:A100<>"",C1:C100))}
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top