How to get Total count in a column excluding Blank Cells in a formula..

[vasanthkumar1979]

Dear Friends,



I need a little help on this. I have entered formula to make a cell return a value when the data is available on other cell. In the attached excel sheet, I have dates in column E. There are some cells where the date will not be available. I wrote a formula in column F, IF(ISBLANK(E5),"","Yes").

In this case, when I make a pivot chart the empty cells in column F comes with blanks count too. Please help how to ignore this. Is there any addition to above formula to replace "" value?

 

[vladimiratanasiu]

Dear Friends,



I need a little help on this. I have entered formula to make a cell return a value when the data is available on other cell. In the attached excel sheet, I have dates in column E. There are some cells where the date will not be available. I wrote a formula in column F, IF(ISBLANK(E5),"","Yes").

In this case, when I make a pivot chart the empty cells in column F comes with blanks count too. Please help how to ignore this. Is there any addition to above formula to replace "" value?

Post a real table, not an image. Use the very common add-in XL2BB - Excel Range to BBCode, or a link of your workbook example uploaded in cloud.

 

[zero269]

Dear Friends,



I need a little help on this. I have entered formula to make a cell return a value when the data is available on other cell. In the attached excel sheet, I have dates in column E. There are some cells where the date will not be available. I wrote a formula in column F, IF(ISBLANK(E5),"","Yes").

In this case, when I make a pivot chart the empty cells in column F comes with blanks count too. Please help how to ignore this. Is there any addition to above formula to replace "" value?

A bit old, but here are two options:
The one on the left is using a standard Data Range, while the one on the right is formatted as a Table.

For the Left Range, you want to use the COUNTA Function:
"The COUNTA function counts the number of cells that are not empty in a range."

COUNTA function - Microsoft Support

Syntax: COUNTA(value1,value2,...)

support.microsoft.com

My example: =COUNTA(B2:B8)
The Table is a better option though. I just had to turn on the Total Row and choose Count from the drop-down list and it does the same thing.

Add a Total row to a table - Microsoft Support

Training: In Microsoft Excel, select the Design tab and then select the Total Row checkbox to add a Total Row. You can also add a function. Watch this video to learn how.

support.microsoft.com

Overview of Excel tables - Microsoft Support

To make managing and analyzing a group of related data easier, you can turn a range of cells into an Excel table (previously known as an Excel list).

support.microsoft.com

VBA Testing.xlsm
	A	B	C	D	E
1	ETA	ETA Provided		ETA	ETA Provided
2	26-Aug	Yes		26-Aug	Yes
3	16-Aug	Yes		16-Aug	Yes
4
5	25-Aug	Yes		25-Aug	Yes
6	23-Aug	Yes		23-Aug	Yes
7
8	26-Aug	Yes		26-Aug	Yes
9		7		Total	7
COUNTA

Cell Formulas
Range	Formula
B2:B8	B2	=IF(ISBLANK($A2),"","Yes")
B9	B9	=COUNTA(B2:B8)
E2:E8	E2	=IF(ISBLANK($D2),"","Yes")
E9	E9	=SUBTOTAL(103,[ETA Provided])