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)

 

[Aladin Akyurek]

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

Book1
	B	C	D	E
4	1	1		6
5	2			6
6	1			6
7	2	0		6
8	1	0
9	2	1
Sheet1

1)

=SUM(B4:B9)-SUMIFS(B4:B9,C4:C9,"")

2)

=SUMIFS(B4:B9,C4:C9,"<>")

If these two do not work for you, C5 or C6 or both are probably not empty or blank. You have perhaps a space in C5 or C6.

3)

=SUM(SUMIFS(B4:B9,C4:C9,{0,1}))

4)

=SUMIFS(B4:B9,C4:C9,"<9.99E+307")

5)

=SUM(IF(ISNUMBER(C4:C9),B4:B9))

which needs to be confirmed with control+shift+enter, not just enter.

 

[RincewindWIZZ]

Thank you for this

There are many ways of getting to the required result in this case. The main interest to me was
- I can use the criteria "" to select all cells that are blank or that have a formula that evaluates to blank ("")
- Why are there no criteria to select all cells that are NOT (blank or that have a formula that evaluates to blank (""))?

And, I suppose, as a supplementary, which is the most computationally efficient?

 

[Aladin Akyurek]

Thank you for this

There are many ways of getting to the required result in this case. The main interest to me was
- I can use the criteria "" to select all cells that are blank or that have a formula that evaluates to blank ("")
- Why are there no criteria to select all cells that are NOT (blank or that have a formula that evaluates to blank (""))?

"<>" allows to select all of the cells which are not empty. This picks out also the formula cells with a formula blank.

The opposite of <> (i.e. NOT) is =, that is:

=SUM(B4:B9)-SUMIFS(B4:B9,C4:C9,"=")

is equivalent to (1).

And, I suppose, as a supplementary, which is the most computationally efficient?

All are faster than (5), but (5) will work with no discussion about what you have in column B.

 

[RincewindWIZZ]

"<>" allows to select all of the cells which are not empty. This picks out also the formula cells with a formula blank.

Not in this case (see post#11) and that is where the issue started

 

[Aladin Akyurek]

Not in this case (see post#11) and that is where the issue started

You seem to confound an empty, unused cell and a cell which houses a formula that returns a "", i.e. a blank.


By the way, you still did not say what you have in C5 and C6 of the C range. Truly empy, unused or a formula or a space...

 

[RincewindWIZZ]

You seem to confound an empty, unused cell and a cell which houses a formula that returns a "", i.e. a blank.

I thought I had made it pretty clear in the initial post that the issue was with cells containing formulae that evaluate to a number or to ""

By the way, you still did not say what you have in C5 and C6 of the C range. Truly empy, unused or a formula or a space...

see post 13

Apologies for any confusion !!

 

[Joe4]

I thought I had made it pretty clear in the initial post that the issue was with cells containing formulae that evaluate to a number or to ""

I had asked multiple times for you to post that formula so we could see it, but you ignored me both times.
I cannot help you if you ignore my questions.

 

[RincewindWIZZ]

I had asked multiple times for you to post that formula so we could see it, but you ignored me both times.
I cannot help you if you ignore my questions.

It is, as I have said multiple time before, a formula that evaluates to a number of blank. For example
=IF(A5="B",1,IF(A5="D",0,""))

The complete spreadsheet is available via the link in an earlier post

 

[Joe4]

It is, as I have said multiple time before, a formula that evaluates to a number of blank.

I know what you said, but I asked to see the formula. The devil is often in the details, and people tend to often overlook important facts or make incorrect assumptions.

The complete spreadsheet is available via the link in an earlier post

I am not able to download files from the internet at my current location, my workplace forbids it for security reasons.

So the formula you posted here:

=IF(A5="B",1,IF(A5="D",0,""))

does not seem to mesh with what you posted in your original question:

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)

What exactly is in column A, numbers or letters?
What exactly is it that you are trying to sum? The values being returned in column B, or some other column?

 

[RincewindWIZZ]

I know what you said, but I asked to see the formula. The devil is often in the details, and people tend to often overlook important facts or make incorrect assumptions.


I am not able to download files from the internet at my current location, my workplace forbids it for security reasons.

So the formula you posted here:

does not seem to mesh with what you posted in your original question:

What exactly is in column A, numbers or letters?
What exactly is it that you are trying to sum? The values being returned in column B, or some other column?

The original question was intended to be generic - I was not expecting the problem to become so difficult
I then posted a sample spreadsheet to demonstrate the issue which is available by a link. In this the columns are different from the generic description
I also posted a screenshot (.jpg) of said spreadsheet for those who did not want to download a spreadsheet

I find posting the actual screenshot too difficult on this website so if you are unable to access either of the above links, I can provide no further information.

Thank you for your interest