# Sumproduct Blank Value Cell Help

#### sbeatton

##### Active Member
I have the following table but am having problems with my count.

My SUMPRODUCT formula is counting cells even if they are blank. For example Total Approved / Assessed Count for A should equal 3, as cell C6 is blank. Same for Total Outstanding Claims, Count A should equal 1 as it is the only Job with a dollar value against it.

Any help much appreciated as I am stumped. Thanks.

Excel Workbook
ABCD
1JobTotal ClaimedTotal Approved / AssessedTotal Outstanding Claims
2A\$6,800.00\$6,800.00
3B\$0.00\$0.00
4A\$5,078.72\$5,078.72
5A\$1,059,844.00\$624,670.50
6A\$0.00\$0.00
7B\$4,650.58\$4,650.58
8
9Total Count651
10Count A444
11Count B222
Sheet1

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### shawnie84

##### New Member
I have the following table but am having problems with my count.

My SUMPRODUCT formula is counting cells even if they are blank. For example Total Approved / Assessed Count for A should equal 3, as cell C6 is blank. Same for Total Outstanding Claims, Count A should equal 1 as it is the only Job with a dollar value against it.

Any help much appreciated as I am stumped. Thanks.

Excel Workbook
ABCD
1JobTotal ClaimedTotal Approved / AssessedTotal Outstanding Claims
2A\$6,800.00\$6,800.00
3B\$0.00\$0.00
4A\$5,078.72\$5,078.72
5A\$1,059,844.00\$624,670.50
6A\$0.00\$0.00
7B\$4,650.58\$4,650.58
8
9Total Count651
10Count A444
11Count B222
Sheet1

for C10
use =SUMPRODUCT(--(\$A\$2:\$A\$7="A"),--(C\$2:C\$7<>""))
the cell is a blank cell and not a cell with value 0.

same for D10
=SUMPRODUCT(--(\$A\$2:\$A\$7="A"),--(D\$2:D\$7<>""))

Last edited:

#### sbeatton

##### Active Member
Thanks Shawnie84,
As you posted I worked it out, slightly differently but same result. Just needed to remember I was looking for numbers!

Excel Workbook
ABCD
1JobTotal ClaimedTotal Approved / AssessedTotal Outstanding Claims
2A\$6,800.00\$6,800.00
3B\$0.00\$0.00
4A\$5,078.72\$5,078.72
5A\$1,059,844.00\$624,670.50
6A\$0.00\$0.00
7B\$4,650.58\$4,650.58
8
9Total Count651
10Count A431
11Count B220
Sheet1

#### shawnie84

##### New Member
my policy as long as it works...

numerous ways to achieve same outcome...

Replies
13
Views
403
Replies
3
Views
225
Replies
5
Views
1K
Replies
3
Views
331
Replies
1
Views
285

1,191,183
Messages
5,985,172
Members
439,944
Latest member
Vangelis74

### 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.

### Which adblocker are you using?

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

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