Sumproduct Blank Value Cell Help

[sbeatton]

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
	A	B	C	D
1	Job	Total Claimed	Total Approved / Assessed	Total Outstanding Claims
2	A	$6,800.00	$6,800.00
3	B	$0.00	$0.00
4	A	$5,078.72	$5,078.72
5	A	$1,059,844.00	$624,670.50
6	A	$0.00		$0.00
7	B	$4,650.58	$4,650.58
8
9	Total Count	6	5	1
10	Count A	4	4	4
11	Count B	2	2	2
Sheet1

 

[shawnie84]

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
	A	B	C	D
1	Job	Total Claimed	Total Approved / Assessed	Total Outstanding Claims
2	A	$6,800.00	$6,800.00
3	B	$0.00	$0.00
4	A	$5,078.72	$5,078.72
5	A	$1,059,844.00	$624,670.50
6	A	$0.00		$0.00
7	B	$4,650.58	$4,650.58
8
9	Total Count	6	5	1
10	Count A	4	4	4
11	Count B	2	2	2
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<>""))

 

[sbeatton]

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
	A	B	C	D
1	Job	Total Claimed	Total Approved / Assessed	Total Outstanding Claims
2	A	$6,800.00	$6,800.00
3	B	$0.00	$0.00
4	A	$5,078.72	$5,078.72
5	A	$1,059,844.00	$624,670.50
6	A	$0.00		$0.00
7	B	$4,650.58	$4,650.58
8
9	Total Count	6	5	1
10	Count A	4	3	1
11	Count B	2	2	0
Sheet1

 

[shawnie84]

my policy as long as it works...

numerous ways to achieve same outcome...