# count non blank values in a column

Hi

I need to count some columns in a particular manner.

Column1
Count the non blank values

Column2
Count  non balnk values when we filter Column1 with only non blank values.

Column3
Count non balnk values when we filter Column1with non blank values and then column2 with non balnk values.

similerly up tp 10 columns

can i do this with excel countifs function..

Thanks
K
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Group Finance ManagerCommented:
How is the data formatted, can you provide a small example of data in a workbook.

You can use this to count blanks on unfiltered columns

=COUNTBLANK(A5:A10)
0
The formula for the first column would be (replacing the range with your range) :
=COUNTIF(A1:A31, "<>" & "")

The formula for the second column would be (replacing the ranges with your ranges):
=COUNTIFS(A1:A31, "<>" & "", B1:B31, "<>" & "")

The formula for the third column would be (replacing the ranges with your ranges):
=COUNTIFS(A1:A31, "<>" & "", B1:B31, "<>" & "", C1:C31, "<>" & "")

The other columns would follow a similar pattern as the above formula.
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Actually, a better option is to use the SUBTOTAL function, if you are using Excel 2016:
=SUBTOTAL(103,A:A)

0
AnalystAuthor Commented:
Hi Shaun,

Many thanks for your reply. I like =Subtotal function. Its working on my office 2013. But how can I apply this 2nd, 3rd etc..?

Regards
K
0
The SubTotal formula only counts cells in the range that are not filtered out. So you can use it for each column, just changing the range reference.
0
AnalystAuthor Commented:
Hi Shaun,

Thanks, I need to count second column after filtering out blank cell of first column

Cheers
0
AnalystAuthor Commented:
Hi Shaun,

Other thing I noticed just now is when countifs counting the date columns changes to text datatype column, shows the last column result

Cheers
0
AnalystAuthor Commented:
Thanks All,
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.