SUMIF criteria for blank field

[scottnmilton]

Example of my problem. Col A contains projected expenses. Col B contains actual expenses (numeric values and blank cells for projects not completed). I need a sumif formula to evaluate Col B and sum all rows in Col A where Col B is blank (no actual expense). I have tried something to the effect of:
=sumif(B2:B150,>'',A2:A150). I am almost positive my problem is the formatting of the criteria. Any help would be appreciated.

Scott

 

[PaddyD]

Hi Scott,

Use a check for isblank:

Book6
	A	B	C	D
1	10	10
2	20
3	30
4	40	40
5	50
6	Total:	100
7
Sheet1

Paddy

 

[Aladin Akyurek]

On 2002-09-10 14:06, scottnmilton wrote:
Example of my problem. Col A contains projected expenses. Col B contains actual expenses (numeric values and blank cells for projects not completed). I need a sumif formula to evaluate Col B and sum all rows in Col A where Col B is blank (no actual expense). I have tried something to the effect of:
=sumif(B2:B150,>'',A2:A150). I am almost positive my problem is the formatting of the criteria. Any help would be appreciated.

Scott

=SUMPRODUCT((LEN(B2:B150)=0)+0,A2:A150)

 

[Mark W.]

Book2
	A	B	C	D	E	F	G	H	I	J	K	L
1	Projected	Actual		525
2	100	150
3	225
4	95	80
5	300
6	55	60
7
Sheet1

This message was edited by Mark W. on 2002-09-10 14:30

 

[Aladin Akyurek]

=SUMIF(B2:B6,"=",A2:A6)

Hmmm... Did you know this?

SUMIF Blanks&NonBlanks.xls
	A	B	C	D	E	F	G
1	2	2.1		8	9	5
2	3
3	4	4.9
4	5
Sheet2

Aladin

 

[Mark W.]

The answer is 5. Cell B2 isn't blank.