Count blank when criteria is met

[Jennyb7714]

I have a report that references an I'd in the first column then another column that specifies the count of a specific item. What I need to do is IF A:A = (reference) then count all non blank cells in column G:G. Can anyone help me out. It seems so simple but I can't get it to work. Oh and col G could be a mixture of formats

Appreciate any and all help

 

[jasonb75]

First thought, probably many alternatives.

Array confirm with Shift Ctrl Enter.

=Sum(if(A:A=reference,--(len(G:G)>0)))

I would suggest maybe A1:A1000 & G1:G1000 or adequate to cover your data, fulll columns will be slow to calculate.

 

[Jennyb7714]

thank you for your reply, it didn't work for me - it only counted everything that was in column A - so it gave me a count of the total cells that = the reference

for example - I have a total of 100 rows that has "120" (ID No) in column A - the cells in column G only has 75 populated cells - but it gave me 100 rather than 75 - does that make sense?

 

[jasonb75]

Do your blank cells have formula in them? If so please one of the formula.

If the data is as you described, and you're entered the formula correctly it should work.

Excel Workbook
	A	B	C	D	E	F	G
2	120		Reference	120			count Me
3	121		Count	6			Don't count me
4	121
5	121						Don't count me
6	121
7	120						count Me
8	121
9	120						count Me
10	120						count Me
11	120
12	119
13	120
14	121						Don't count me
15	120
16	119
17	120						count Me
18	121
19	120						count Me
20	119
Sheet

 

[Jennyb7714]

<TABLE style="WIDTH: 288pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=384 border=0><COLGROUP><COL style="WIDTH: 48pt" span=6 width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-RIGHT: #d0e3d3; BORDER-TOP: #d0e3d3; BORDER-LEFT: #d0e3d3; WIDTH: 48pt; BORDER-BOTTOM: #d0e3d3; HEIGHT: 14.4pt; BACKGROUND-COLOR: transparent" width=64 height=19></TD><TD style="BORDER-RIGHT: #d0e3d3; BORDER-TOP: #d0e3d3; BORDER-LEFT: #d0e3d3; WIDTH: 48pt; BORDER-BOTTOM: #d0e3d3; BACKGROUND-COLOR: transparent" width=64></TD><TD style="BORDER-RIGHT: #d0e3d3; BORDER-TOP: #d0e3d3; BORDER-LEFT: #d0e3d3; WIDTH: 48pt; BORDER-BOTTOM: #d0e3d3; BACKGROUND-COLOR: transparent" width=64></TD><TD style="BORDER-RIGHT: #d0e3d3; BORDER-TOP: #d0e3d3; BORDER-LEFT: #d0e3d3; WIDTH: 48pt; BORDER-BOTTOM: #d0e3d3; BACKGROUND-COLOR: transparent" width=64></TD><TD style="BORDER-RIGHT: #d0e3d3; BORDER-TOP: #d0e3d3; BORDER-LEFT: #d0e3d3; WIDTH: 48pt; BORDER-BOTTOM: #d0e3d3; BACKGROUND-COLOR: transparent" width=64></TD><TD style="BORDER-RIGHT: #d0e3d3; BORDER-TOP: #d0e3d3; BORDER-LEFT: #d0e3d3; WIDTH: 48pt; BORDER-BOTTOM: #d0e3d3; BACKGROUND-COLOR: transparent" width=64></TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-RIGHT: #d0e3d3; BORDER-TOP: #d0e3d3; BORDER-LEFT: #d0e3d3; BORDER-BOTTOM: #d0e3d3; HEIGHT: 14.4pt; BACKGROUND-COLOR: transparent" height=19></TD><TD style="BORDER-RIGHT: #d0e3d3; BORDER-TOP: #d0e3d3; BORDER-LEFT: #d0e3d3; BORDER-BOTTOM: #d0e3d3; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d0e3d3; BORDER-TOP: #d0e3d3; BORDER-LEFT: #d0e3d3; BORDER-BOTTOM: #d0e3d3; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d0e3d3; BORDER-TOP: #d0e3d3; BORDER-LEFT: #d0e3d3; BORDER-BOTTOM: #d0e3d3; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d0e3d3; BORDER-TOP: #d0e3d3; BORDER-LEFT: #d0e3d3; BORDER-BOTTOM: #d0e3d3; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d0e3d3; BORDER-TOP: #d0e3d3; BORDER-LEFT: #d0e3d3; BORDER-BOTTOM: #d0e3d3; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.4pt; BACKGROUND-COLOR: transparent" height=19>ID</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Count</TD><TD style="BORDER-RIGHT: #d0e3d3; BORDER-TOP: #d0e3d3; BORDER-LEFT: #d0e3d3; BORDER-BOTTOM: #d0e3d3; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d0e3d3; BORDER-TOP: #d0e3d3; BORDER-LEFT: #d0e3d3; BORDER-BOTTOM: #d0e3d3; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d0e3d3; BORDER-TOP: #d0e3d3; BORDER-LEFT: #d0e3d3; BORDER-BOTTOM: #d0e3d3; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d0e3d3; BORDER-TOP: #d0e3d3; BORDER-LEFT: #d0e3d3; BORDER-BOTTOM: #d0e3d3; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.4pt; BACKGROUND-COLOR: #f2dddc" align=right height=19>113</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #f2dddc">23</TD><TD style="BORDER-RIGHT: #d0e3d3; BORDER-TOP: #d0e3d3; BORDER-LEFT: #d0e3d3; BORDER-BOTTOM: #d0e3d3; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d0e3d3; BORDER-TOP: #d0e3d3; BORDER-LEFT: #d0e3d3; BORDER-BOTTOM: #d0e3d3; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d0e3d3; BORDER-TOP: #d0e3d3; BORDER-LEFT: #d0e3d3; BORDER-BOTTOM: #d0e3d3; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d0e3d3; BORDER-TOP: #d0e3d3; BORDER-LEFT: #d0e3d3; BORDER-BOTTOM: #d0e3d3; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.4pt; BACKGROUND-COLOR: #f2dddc" align=right height=19>113</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #f2dddc">7</TD><TD style="BORDER-RIGHT: #d0e3d3; BORDER-TOP: #d0e3d3; BORDER-LEFT: #d0e3d3; BORDER-BOTTOM: #d0e3d3; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d0e3d3; BORDER-TOP: #d0e3d3; BORDER-LEFT: #d0e3d3; BORDER-BOTTOM: #d0e3d3; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d0e3d3; BORDER-TOP: #d0e3d3; BORDER-LEFT: #d0e3d3; BORDER-BOTTOM: #d0e3d3; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d0e3d3; BORDER-TOP: #d0e3d3; BORDER-LEFT: #d0e3d3; BORDER-BOTTOM: #d0e3d3; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.4pt; BACKGROUND-COLOR: #f2dddc" align=right height=19>113</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #f2dddc"> </TD><TD style="BORDER-RIGHT: #d0e3d3; BORDER-TOP: #d0e3d3; BORDER-LEFT: #d0e3d3; BORDER-BOTTOM: #d0e3d3; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d0e3d3; BORDER-TOP: #d0e3d3; BORDER-LEFT: #d0e3d3; BORDER-BOTTOM: #d0e3d3; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d0e3d3; BORDER-TOP: #d0e3d3; BORDER-LEFT: #d0e3d3; BORDER-BOTTOM: #d0e3d3; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d0e3d3; BORDER-TOP: #d0e3d3; BORDER-LEFT: #d0e3d3; BORDER-BOTTOM: #d0e3d3; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.4pt; BACKGROUND-COLOR: #f2dddc" align=right height=19>113</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #f2dddc"> </TD><TD style="BORDER-RIGHT: #d0e3d3; BORDER-TOP: #d0e3d3; BORDER-LEFT: #d0e3d3; BORDER-BOTTOM: #d0e3d3; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d0e3d3; BORDER-TOP: #d0e3d3; BORDER-LEFT: #d0e3d3; BORDER-BOTTOM: #d0e3d3; BACKGROUND-COLOR: transparent" align=right>113</TD><TD class=xl65 style="BORDER-RIGHT: #d0e3d3; BORDER-TOP: #d0e3d3; BORDER-LEFT: #d0e3d3; BORDER-BOTTOM: #d0e3d3; BACKGROUND-COLOR: transparent" align=right>2

</TD><TD class=xl65 id=td_post_2681474 style="BORDER-RIGHT: #d0e3d3; BORDER-TOP: #d0e3d3; BORDER-LEFT: #d0e3d3; BORDER-BOTTOM: #d0e3d3; BACKGROUND-COLOR: transparent">{=SUM(IF($A:$A=$D7,--(LEN($B:$B)>0)))}

</TD></TR>

<TR style="HEIGHT: 14.4pt" height=19><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.4pt; BACKGROUND-COLOR: #eaf1dd" align=right height=19>114</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #eaf1dd">11</TD><TD style="BORDER-RIGHT: #d0e3d3; BORDER-TOP: #d0e3d3; BORDER-LEFT: #d0e3d3; BORDER-BOTTOM: #d0e3d3; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d0e3d3; BORDER-TOP: #d0e3d3; BORDER-LEFT: #d0e3d3; BORDER-BOTTOM: #d0e3d3; BACKGROUND-COLOR: transparent" align=right>114</TD><TD class=xl65 style="BORDER-RIGHT: #d0e3d3; BORDER-TOP: #d0e3d3; BORDER-LEFT: #d0e3d3; BORDER-BOTTOM: #d0e3d3; BACKGROUND-COLOR: transparent" align=right>3</TD><TD class=xl65 style="BORDER-RIGHT: #d0e3d3; BORDER-TOP: #d0e3d3; BORDER-LEFT: #d0e3d3; BORDER-BOTTOM: #d0e3d3; BACKGROUND-COLOR: transparent">{=SUM(IF($A:$A=$D8,--(LEN($B:$B)>0)))}</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.4pt; BACKGROUND-COLOR: #eaf1dd" align=right height=19>114</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #eaf1dd">2</TD><TD style="BORDER-RIGHT: #d0e3d3; BORDER-TOP: #d0e3d3; BORDER-LEFT: #d0e3d3; BORDER-BOTTOM: #d0e3d3; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d0e3d3; BORDER-TOP: #d0e3d3; BORDER-LEFT: #d0e3d3; BORDER-BOTTOM: #d0e3d3; BACKGROUND-COLOR: transparent" align=right>156</TD><TD class=xl65 style="BORDER-RIGHT: #d0e3d3; BORDER-TOP: #d0e3d3; BORDER-LEFT: #d0e3d3; BORDER-BOTTOM: #d0e3d3; BACKGROUND-COLOR: transparent" align=right>2</TD><TD class=xl65 style="BORDER-RIGHT: #d0e3d3; BORDER-TOP: #d0e3d3; BORDER-LEFT: #d0e3d3; BORDER-BOTTOM: #d0e3d3; BACKGROUND-COLOR: transparent">{=SUM(IF($A:$A=$D9,--(LEN($B:$B)>0)))}</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.4pt; BACKGROUND-COLOR: #eaf1dd" align=right height=19>114</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #eaf1dd"> </TD><TD style="BORDER-RIGHT: #d0e3d3; BORDER-TOP: #d0e3d3; BORDER-LEFT: #d0e3d3; BORDER-BOTTOM: #d0e3d3; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d0e3d3; BORDER-TOP: #d0e3d3; BORDER-LEFT: #d0e3d3; BORDER-BOTTOM: #d0e3d3; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d0e3d3; BORDER-TOP: #d0e3d3; BORDER-LEFT: #d0e3d3; BORDER-BOTTOM: #d0e3d3; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d0e3d3; BORDER-TOP: #d0e3d3; BORDER-LEFT: #d0e3d3; BORDER-BOTTOM: #d0e3d3; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.4pt; BACKGROUND-COLOR: #e5e0ec" align=right height=19>156</TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #e5e0ec" align=right>2</TD><TD style="BORDER-RIGHT: #d0e3d3; BORDER-TOP: #d0e3d3; BORDER-LEFT: #d0e3d3; BORDER-BOTTOM: #d0e3d3; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d0e3d3; BORDER-TOP: #d0e3d3; BORDER-LEFT: #d0e3d3; BORDER-BOTTOM: #d0e3d3; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d0e3d3; BORDER-TOP: #d0e3d3; BORDER-LEFT: #d0e3d3; BORDER-BOTTOM: #d0e3d3; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d0e3d3; BORDER-TOP: #d0e3d3; BORDER-LEFT: #d0e3d3; BORDER-BOTTOM: #d0e3d3; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.4pt; BACKGROUND-COLOR: #e5e0ec" align=right height=19>156</TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #e5e0ec"> </TD><TD style="BORDER-RIGHT: #d0e3d3; BORDER-TOP: #d0e3d3; BORDER-LEFT: #d0e3d3; BORDER-BOTTOM: #d0e3d3; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d0e3d3; BORDER-TOP: #d0e3d3; BORDER-LEFT: #d0e3d3; BORDER-BOTTOM: #d0e3d3; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d0e3d3; BORDER-TOP: #d0e3d3; BORDER-LEFT: #d0e3d3; BORDER-BOTTOM: #d0e3d3; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d0e3d3; BORDER-TOP: #d0e3d3; BORDER-LEFT: #d0e3d3; BORDER-BOTTOM: #d0e3d3; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.4pt; BACKGROUND-COLOR: #e5e0ec" align=right height=19>156</TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #e5e0ec" align=right>5</TD><TD style="BORDER-RIGHT: #d0e3d3; BORDER-TOP: #d0e3d3; BORDER-LEFT: #d0e3d3; BORDER-BOTTOM: #d0e3d3; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d0e3d3; BORDER-TOP: #d0e3d3; BORDER-LEFT: #d0e3d3; BORDER-BOTTOM: #d0e3d3; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d0e3d3; BORDER-TOP: #d0e3d3; BORDER-LEFT: #d0e3d3; BORDER-BOTTOM: #d0e3d3; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d0e3d3; BORDER-TOP: #d0e3d3; BORDER-LEFT: #d0e3d3; BORDER-BOTTOM: #d0e3d3; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.4pt; BACKGROUND-COLOR: #e5e0ec" align=right height=19>156</TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #e5e0ec"> </TD><TD style="BORDER-RIGHT: #d0e3d3; BORDER-TOP: #d0e3d3; BORDER-LEFT: #d0e3d3; BORDER-BOTTOM: #d0e3d3; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d0e3d3; BORDER-TOP: #d0e3d3; BORDER-LEFT: #d0e3d3; BORDER-BOTTOM: #d0e3d3; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d0e3d3; BORDER-TOP: #d0e3d3; BORDER-LEFT: #d0e3d3; BORDER-BOTTOM: #d0e3d3; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d0e3d3; BORDER-TOP: #d0e3d3; BORDER-LEFT: #d0e3d3; BORDER-BOTTOM: #d0e3d3; BACKGROUND-COLOR: transparent"></TD></TR></TBODY></TABLE>


Not sure if you're able to see this, but it works for two criteria, but not for the one in the middle - 114 - can you see what I'm doing wrong - I did the Ctl Shift Enter thing on all three - check and checked.....

 

[jasonb75]

Strange, copying your table I get 2, What happens if you restrict the range?

{=SUM(IF($A$1:$A$12=$D8,--(LEN($B:$B)>0)))}

 

[Jennyb7714]

see, that's the strange thing - I tried that, but I got the same result - this is terribly frustrating - I tried countifs too - but that wouldn't give me a consistent result.

 

[jasonb75]

Check the blank cells, to any of them contain spaces, or a formula that would return a space?

does {=SUM(IF(A:A=$D8,--(LEN(TRIM(G:G))>0)))} return the correct count?

or {=SUM(IF(A:A=$D8,--(LEN(CLEAN(G:G))>0)))}

edit:- what did you try with countifs?

=COUNTIFS(A:A,D2,G:G,"<>") should work if the blank cells are empty, but not if they are formula blank.

 

[Jennyb7714]

Whohoo! I think you cracked it - that did it that time - Now I will try it on the main spreadsheet
THANK YOU THANK YOU THANK YOU.....
Have a superb day!

 

[jasonb75]

Which one worked?

If it was {=SUM(IF(A:A=$D8,--(LEN(TRIM(G:G))>0)))}

Then i think =COUNTIFS(A:A,$D8,G:G,">=<") should, but without the calculation delay.