Formula to count how many cells contain data

[Dazzawm]

I have data laid out as below, I need a formula that will count how many cells contain data please.

Excel 2010

	Y	Z	AA	AB	AC	AD	AE	AF
TEE06	TEE03	91108	91107	91106	91104	91102	91101
TEE06			91107	91106	91104	91102	91101

<colgroup><col style="width: 25pxpx" https:="" www.mrexcel.com="" forum="" usertag.php?do="list&action=hash&hash=DAE7F5"" target="_blank"></colgroup><colgroup><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

</tbody>

Sheet1​

In this instance i would have a result of 8 in column AG2 and 6 in AG3

 

[AlanY]

try the counta() function

 

[Dazzawm]

That's not giving anywhere near the desired results. Its giving 8 when there is just one cell with data, 1 when there is 2 cells with data...

 

[Ingolf]

That's not giving anywhere near the desired results. Its giving 8 when there is just one cell with data, 1 when there is 2 cells with data...

This mean, that your data are result of a formula. You have to specify this in your first post.
You should have known (you have 3132 posts)

If your date are write in cells then counta formula is good.

 

[AlanY]

try if this work

Excel 2013/2016
	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH
1
2	TEE06	TEE03	91108	91107	91106	91104	91102	91101		8
3	TEE06			91107	91106	91104	91102	91101		6
4
Sheet1

Cell Formulas
Range	Formula
AH2	{=SUM(--(LEN(TRIM(Y2:AF2))>0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Ingolf]

I think, is suffice:

{=SUM(--(LEN(A1:H1)>0))}

Entered with Ctrl+Shift+Enter.

 

[Dazzawm]

You should have known (you have 3132 posts)

Why should I? If I knew I wouldn't have been on requesting help.

 

[Dazzawm]

try if this work

Excel 2013/2016

	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH
TEE06	TEE03
TEE06

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]91108[/TD]
[TD="align: right"]91107[/TD]
[TD="align: right"]91106[/TD]
[TD="align: right"]91104[/TD]
[TD="align: right"]91102[/TD]
[TD="align: right"]91101[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]8[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"]91107[/TD]
[TD="align: right"]91106[/TD]
[TD="align: right"]91104[/TD]
[TD="align: right"]91102[/TD]
[TD="align: right"]91101[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]6[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AH2[/TH]
[TD="align: left"]{=SUM(--(LEN(TRIM(Y2:AF2))>0))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

Thanks that worked.

 

[AlanY]

you're welcome

 

[Ingolf]

Why should I? If I knew I wouldn't have been on requesting help.

Ahhhh, I forgot, you do not have to think, you are interested only how to get a solution to your problem, and it does not matter that those who want to help you lose time wondering why the solution does not work. For you, the others matter, only, if you get the answer you want.