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

[jasonconlon]

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?

[Anne Troy]

Try isnull

[jasonconlon]

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

[jasonconlon]

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--

[Anne Troy]

Way to go!

[Turk183turk183]

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

[alvinwlh]

Hi,

Try:

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

will be 
{=SUM(IF(A1:A100<>"",C1:C100))}