SUMIF and non-blank

[JanetW]

Is there a way to use SUMIF (or something similar) with the criteri
being non-blank cells (including cells whose formulas return "" )? S
something like:

= SUMIF(A:A,<>"",B:B) (which of course doesn't work)

I'm trying to avoid using the "{=SUM(IF(...))}" array formula because
and B are unknown length. So to accomodate all possibilities, sinc
array formulas don't take whole column references (A:A, B:B), I'd hav
to write:

{=SUM(IF(A1:A65536<>"",B1:B65536,0))}

which can incur a serious performance penalty when calculating th
spreadsheet (and is a bit ridiculous for cases and A & B are only 2
rows long!)

 

[Peo Sjoblom]

One way

=SUMIF(A:A,"<>",B:B)

 

[Frank Kabel]

another way

 

[JanetW]

For,

=SUMIF(A:A,"<>",B:B)

it appears that it is still considering a cell with a formula tha
returns a blank ( "" ) as a non-blank cell (which is a rather annoyin
"feature" of Excel to consider such a results as a non-blank!). I
there any other way to refer to a blank that might work? Is there an
sort of character code that I might be able to use ("<>code")?

And,

=SUMPRODUCT(($A$1:$A$9999<>"")*($B$1:$B$9999))

works, but like the array formulas, SUMPRODUCT does not accept whol
column referrences (ie, A:A). There are a large number of thes
formulas on this spreadsheet, and since I have no way of knowing ho
many rows of data the user will be working with (potentially 10s o
thousands), I'd have to set the formula to a rather high row number
which would nearly cripple the spreadsheet with recalc delays (manua
recalculation is not an option). It seems that Excel can intelligentl
determine where the data ends if you use A:A, as opposed to usin
A1:A65532 which seems to explicitly causes every row to be calculated

 

[Peo Sjoblom]

Look at Frank's solution using sumproduct

 

[JanetW]

Thanks, but as I mentioned in my previous post, SUMPRODUCT has the th
same limitations and problems as using an array formula.

 

[Harlan Grove]

Is there a way to use SUMIF (or something similar) with the criteria
being non-blank cells (including cells whose formulas return "" )? So
something like:

= SUMIF(A:A,<>"",B:B) (which of course doesn't work)

You could try

=SUMIF(A:A,">?*",B:B)

which will select only rows in which col A contains text strings other than ""
or only spaces, e.g., " " and " ". If you want to include such visually blank
cells as well, try

=SUMIF(A1:A100,"?*",B1:B100)

If column A contains just numbers and 'blank' cells, then you could use

=SUMIF(A1:A100,">-1E300",B1:B100)

If column A contains numbers, visible text and 'blank' cells, then one way to do
this would be

=SUM(B1:B100)-SUMIF(A1:A100,"",B1:B100)

I'm trying to avoid using the "{=SUM(IF(...))}" array formula because A
and B are unknown length. So to accomodate all possibilities, since
array formulas don't take whole column references (A:A, B:B), I'd have
to write:

{=SUM(IF(A1:A65536<>"",B1:B65536,0))}

Actually those are still whole column references, and you'll find that Excel
converts them into A:A and B:B.

which can incur a serious performance penalty when calculating the
spreadsheet (and is a bit ridiculous for cases and A & B are only 20
rows long!).

Actually, Excel is pretty nimble about skipping empty cells when iterating
through ranges. Anyway, you could address this using OFFSET.

OFFSET(A:A,0,0,MAX(MATCH(1E+300,A:A),MATCH(REPT("z",255),A:A)),1)

returns the portion of column A from row 1 to the last (bottommost) row with a
number or text value in column A.

 

[JanetW]

Harlan,

I'll have to stare at your OFFSET formula a bit longer to figure ou
what it's doing, but I think that it will be useful for othe
situations. But for my current issue, your first suggestion was jus
what I was looking for (col A is either text or "" as determined by
formula). Thanks!!!

PS: As for the "whole column" thing, I must confess that I neve
actually tried referencing to row 65532. I've done large reference
such as A1:A50000 within array formulas before which caused a ver
annoying delay as the spreadsheet recalcuated after every data entr
(which in this case would be very frequently). But when I started usin
functions such as SUMIF which could accept A:A references in place o
the array formulas things sped up considerably.

"JanetW <" wrote...

Is there a way to use SUMIF (or something similar) with th criteria
being non-blank cells (including cells whose formulas return "" ) So
something like:

= SUMIF(A:A,<>"",B:B) (which of course doesn't work)

You could try

=SUMIF(A:A,">?*",B:B)

which will select only rows in which col A contains text string
other than ""
or only spaces, e.g., " " and " "