COUNTA Range Exclude Formulas

[ozbeachbum]

Hi All,
I am trying to count the number of non blank cells in 2 ranges and exclude formulas.
Attached is a sample sheet.
Any assistance would be greatly appreciated.

count not formula.xlsx
	B	C	D	E
2	Current Entries			Should show: NO Current Entries
3
4	0	0
5
6
7
8
9
10	0	0
11
12
13
14
15
16	0	0
17
18
19
20
Sheet1

Cell Formulas
Range	Formula
B2	B2	=IF(AND(COUNTA(B4:B20,C4:C20))>0,"Current Entries","No Current Entries")
B16:C16,B10:C10,B4:C4	B4	=COUNTA(B5:B8)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:C2	Expression	=$B$2="Current Entries"	text	NO
C16	Cell Value	>0	text	NO
B16	Cell Value	>0	text	NO
C10	Cell Value	>0	text	NO
C4	Cell Value	>0	text	NO
B10	Cell Value	>0	text	NO
B4	Cell Value	>0	text	NO

 

[jasonb75]

This will do what you are asking. Note that this is done in Office 365. If you are still using Excel 2010 as shown on your profile then you may need to array confirm the formula using Ctrl Shift Enter. If this is the case then you would need to unmerge B2:C2 first as you can not enter an array formula in a merged cell.

Book1
	B	C	D	E
2	No Current Entries			Should show: NO Current Entries
3
4	0	0
5
6
7
8
9
10	0	0
11
12
13
14
15
16	0	0
17
18
19
20
Sheet2

Cell Formulas
Range	Formula
B2	B2	=IF(COUNTA(B4:C20)-SUM(IFERROR(--(LEFT(FORMULATEXT(B4:C20),1)="="),0)),"Current Entries","No Current Entries")
B4:C4,B16:C16,B10:C10	B4	=COUNTA(B5:B8)

 

[ozbeachbum]

Hi Jason,
Thanks for your reply.
I tried both array as suggested and non array, and in both instances the result is still returning Current Entries.
Any suggestions?
I have reattached the worksheet with your formula.
Cheers,
Dave.

count not formula.xlsx
	B	C	D	E	F	G
2	Current Entries			Should show: NO Current Entries
3
4	0	0		Current Entries		Non Array
5
6				Current Entries		Array
7
8
9
10	0	0
11
12
13
14
15
16	0	0
17
18
19
20
Sheet1

Cell Formulas
Range	Formula
B2	B2	=IF(COUNTA(B4:C20)-SUM(IFERROR(--(LEFT(FORMULATEXT(B4:C20),1)="="),0)),"Current Entries","No Current Entries")
B16:C16,B10:C10,B4:C4	B4	=COUNTA(B5:B8)
E4	E4	=IF(COUNTA(B4:C20)-SUM(IFERROR(--(LEFT(FORMULATEXT(B4:C20),1)="="),0)),"Current Entries","No Current Entries")
E6	E6	=IF(COUNTA(B4:C20)-SUM(IFERROR(--(LEFT(FORMULATEXT(B4:C20),1)="="),0)),"Current Entries","No Current Entries")
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:C2	Expression	=$B$2="Current Entries"	text	NO
C16	Cell Value	>0	text	NO
B16	Cell Value	>0	text	NO
C10	Cell Value	>0	text	NO
C4	Cell Value	>0	text	NO
B10	Cell Value	>0	text	NO
B4	Cell Value	>0	text	NO

 

[jasonb75]

Try =FORMULATEXT(B4) in an empty cell, does that show you the COUNTA() formula, or a #NAME? error?

If it shown an error then that means your version of excel doesn't support the function that the formula needs. Without that one, I don't think it will be possible unless you use vba.

 

[ozbeachbum]

Hi Jason,
It shows #NAME?
Thank you for all your effort.
Cheers,
Dave.

 

[jasonb75]

When I looked at this initially, I was working on the basis that this

I am trying to count the number of non blank cells in 2 ranges and exclude formulas.

could mean any formula.

If you are only ever going to use things like simple counts as in your example then you could use either SUBTOTAL or AGGREGATE (aggregate has a few more options if needed).

I really need to stop overthinking things

Book1
	B	C
2	No Current Entries
3
4	0	0
5
6
7
8
9
10	0	0
11
12
13
14
15
16	0	0
17
18
19
20
Sheet1

Cell Formulas
Range	Formula
B2	B2	=IF(SUBTOTAL(3,B4:C20),"Current Entries","No Current Entries")
B4:C4,B16:C16,B10:C10	B4	=SUBTOTAL(3,B5:B8)