# SUMIF criteria for blank field

#### scottnmilton

##### New Member
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

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Hi Scott,

Use a check for isblank:
Book6
ABCD
11010
220
330
44040
550
6Total:100
7
Sheet1

Paddy

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)

Book2
ABCDEFGHIJKL
1ProjectedActual525
2100150
3225
49580
5300
65560
7
Sheet1

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

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

Hmmm... Did you know this?
SUMIF Blanks&NonBlanks.xls
ABCDEFG
122.1895
23
344.9
45
Sheet2

Aladin

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

Replies
4
Views
122
Replies
6
Views
247
Replies
6
Views
588
Replies
1
Views
638
Replies
3
Views
364

Threads
1,218,620
Messages
6,143,516
Members
450,492
Latest member
Rusbus1972

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back