Sum if not blank

[RincewindWIZZ]

So I have the following data:
A1:A10 all contain 1 (for simplicity)
B1 to B10 contains formula that evaluate either to a number or to ""
Lets say 8 evaluate to a number (some of which are 0) and 2 evaluate to ""


I want to sum all the cells in A for which the corresponding cell in B is not "" so I tried
SUMIF(A1:A10,criterion,B1:B10)

Criterion Result
"". . . . . . . . gives . . . 2
"<>" . . . . . .gives. . . 10
"<>"&"". . . .gives. . . 10
">""". . . . . .gives. . . .0
">=""". . . . gives. . . .0
">"&"". . . . gives. . . . 0
"<>"&C1. . .gives. . . 10 (c1 contains ="")
So what criterion should I use?

(OK there are other ways I can do this but it seems odd to me that I cannot find an appropriate criterion for SUMIF)

 

[RincewindWIZZ]

Humour me please and try that formula in the spreadsheet linked to above.
When I put =SUMIF(C4:C9,"<>",B4:B9) into D3 I get 9!! but it should be 6

If I delete the contents of C5 and C6 I get the required value 6 but that does not help.
C5 and C6 have formulae that evaluate to "" which is clearly not hte same thing!

 

[Fluff]

I don't trust that site, as I have had bad experiences of them trying downloading rubbish before.
But if it's the same layout as I used in post#10, I suspect that the blank cells aren't blank.

If you put this in d5
=ISBLANK(C5)
what does it say?

 

[RincewindWIZZ]

No, they are not blank.


If I may quote from my original post . . . .
B1 to B10 contains formula that evaluate either to a number or to ""

 

[Aladin Akyurek]

OK so it seems I have to do it this way

So the question is what criterion do I put into the SUMIF formula in D4 to give the answer 6 which is the sum of the entries in Col B for which Col C is not blank

=SUMIFS(B:B,C:C,"<>")

should do.But if column B contains formulas, control+shift+enter, not just enter:

=SUM(IF(1-($C$2:$C$100=""),IF(ISNUMBER(1/$B$2:$B$100),$B$2:$B$100+0)))

 

[Joe4]

No, they are not blank.


If I may quote from my original post . . . .
B1 to B10 contains formula that evaluate either to a number or to ""

Can you please post the formula in column B like I asked originally?
If we see exactly what it is doing, I think it will probably become evident to us what is going on.
Many people mistakenly think that a single space is the same as a blank ("" is NOT the same as " ").

 

[RincewindWIZZ]

=SUMIFS(B:B,C:C,"<>")

should do

Sadly not. Just gives 9 again.

If it is not possible to provide a criterion for the SUMIF, probably easiest is to sum the lot and subtract the ones that are blank because, bizarrely, =SUMIF(C4:C11,"",B4:B11) produces 3, the correct sum of the cells evaluating to blank!

I can find no way acceptable to Excel of having a criterion that is NOT ""

 

[Fluff]

How about
=SUM(SUMIF(C4:C9,{">=0","<0"},B4:B9))

 

[RincewindWIZZ]

That works.

But how? What is the significance of { } - since its not an array formula

And would it be quicker or slower than the sum - sumif approach?

But then =SUMIF(C4:C9,">=0",B4:B9) seems also to work and is simpler (and robust since values in C can only be numeric or "")

 

[Fluff]

As long as you cannot have negative values in col C, I'd use your formula.
The formula from post#17 if effectively doing 2 sumifs which are then added together by the SUM function.
It's doing a SUMIF if col C ">=0" OR "<0"

 

[RincewindWIZZ]

The formula from post#17 if effectively doing 2 sumifs which are then added together by the SUM function

Interesting. Had not encountered that construction before. Thank you
Only one condition, no need for external SUM
More than one condition, external Sum

And if you don't do an external sum you presumably get an array with one value per condition?