Counta Range of text leaving out blanks

[brianfosterblack]

I have a range of cells in which each cell has a formula. the formula will result in ether text being entered in a cell or leave it blank (in other words ="")
I want to count all the cells which are not blank and all will have text or dates in them - no numbers
If I use

counta(B3:B6)

It counts the blanks and I get an answer of 4 when only 1 cell has text in.
If I use

counta(B3:B6)-countif(B3:B6,"")

I get the correct answer of 1
However I want to enter the formula to extend up to column B28 for staff who will later use these columns to enter more date but in this case the second formula does not work as there are many more blank cells
Can anyone please help.

 

[Phuoc]

Try this:

=SUMPRODUCT(--(LEN(B3:B28)>0))

 

[jorismoerings]

This is other option:

=COUNTA(B3:B28)-COUNTBLANK(B3:B28)-COUNT(B3:B28)

 

[Muhammad_Usman]

This Might be work for you

24-08-21 Exp.xlsx
	J	K	L	M	N	O
3	1	1		10		6
4				6
5
6	4	4
7	5	5
8
9	7	7
10	8	8
11
12	10	10
Sheet2

Cell Formulas
Range	Formula
O3	O3	=COUNT(K3:K12,ISNUMBER(K3:K12))
M3	M3	=COUNTA(K3:K12)
M4	M4	=COUNTA(K3:K12)-COUNTIFS(K3:K12,"")
K3:K12	K3	=IF(ISNUMBER(J3),J3,"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[mlcalves]

I have a range of cells in which each cell has a formula. the formula will result in ether text being entered in a cell or leave it blank (in other words ="")
I want to count all the cells which are not blank and all will have text or dates in them - no numbers
If I use

counta(B3:B6)

It counts the blanks and I get an answer of 4 when only 1 cell has text in.
If I use

counta(B3:B6)-countif(B3:B6,"")

I get the correct answer of 1
However I want to enter the formula to extend up to column B28 for staff who will later use these columns to enter more date but in this case the second formula does not work as there are many more blank cells
Can anyone please help.

hi,

you don't want to count numbers because you obtain 0 in formula, right?

i did this:

in column A i putted:

=IF(B1="";"";B1)

in your case, it will be another cell

And than the formula is:

=SUMPRODUCT((A1:A20<>"")+0)

 

[brianfosterblack]

Try this:

=SUMPRODUCT(--(LEN(B3:B28)>0))

Thanks for getting back to me but I get a #Value! error. I have checked carefully that I have the formula exactly as you have it here

 

[jorismoerings]

Try this:

=SUMPRODUCT(--(LEN(B3:B28)>0))

Although this can work, this doesn't exclude the cells with a number from the count, like the OP asked for.
And on that remark:

will have text or dates in them

You've mentioned this. Will the date mentioned in the cell be a date formatted as date (which Excel will consider as a number) or will it be a date in a text field( hence considered as a textstring)?

 

[brianfosterblack]

Although this can work, this doesn't exclude the cells with a number from the count, like the OP asked for.
And on that remark:



You've mentioned this. Will the date mentioned in the cell be a date formatted as date (which Excel will consider as a number) or will it be a date in a text field( hence considered as a textstring)?

I My dates are formatted as dates but I tried the formula Count (Range) and it counted the dates.

 

[brianfosterblack]

hi,

you don't want to count numbers because you obtain 0 in formula, right?

i did this:

in column A i putted:

=IF(B1="";"";B1)

in your case, it will be another cell

And than the formula is:

=SUMPRODUCT((A1:A20<>"")+0)

This works perfectly. Thank you all for your help. Much appreciated

        =SUMPRODUCT((A1:A20<>"")+0)