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..

Please let me know

Thanks
K
Asatoma SadgamayaAnalystAsked:
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.

Roy CoxGroup 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
Shaun KlineLead Software EngineerCommented:
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

Your issues matter to us.

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

Start your 7-day free trial
Shaun KlineLead Software EngineerCommented:
Actually, a better option is to use the SUBTOTAL function, if you are using Excel 2016:
=SUBTOTAL(103,A:A)

You can read about this function here:
https://support.office.com/en-us/article/subtotal-function-7b027003-f060-4ade-9040-e478765b9939?NS=EXCEL&Version=16&SysLcid=1033&UiLcid=1033&AppVer=ZXL160&HelpId=xlmain11.chm60392&ui=en-US&rs=en-US&ad=US
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Asatoma SadgamayaAnalystAuthor 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
Shaun KlineLead Software EngineerCommented:
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
Asatoma SadgamayaAnalystAuthor Commented:
Hi Shaun,

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

Cheers
0
Asatoma SadgamayaAnalystAuthor 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
Asatoma SadgamayaAnalystAuthor 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.