# COUNT cells and IF ALL cells are blank do not display 0

1. ## COUNT cells and IF ALL cells are blank do not display 0

Dear Gurus! Your assistance please on the below....

I need to count cells with numbers only ignoring any text but if ALL CELLS within the range are blank or contain text only then i do not want the formula to display 0. it needs just to leave it blank.

2. ## Re: COUNT cells and IF ALL cells are blank do not display 0

try using subtotal():
SUBTOTAL(2,A1:A10)
where 2 - counting only numbers

3. ## Re: COUNT cells and IF ALL cells are blank do not display 0

Maybe something like this...

=IFERROR(1/(1/COUNT(A2:A10)),"")

Adjust the range to suit.

4. ## Re: COUNT cells and IF ALL cells are blank do not display 0

And it will not display 0? Thanks a lot!!!

5. ## Re: COUNT cells and IF ALL cells are blank do not display 0

Originally Posted by Abu Abdurakhim
And it will not display 0?
Nope. No zeros!

Thanks a lot!!!
You're welcome!

6. ## Re: COUNT cells and IF ALL cells are blank do not display 0

It does work but i need it also to show zero when all cells in the range contain text only.
For example if in the past months Jan, Feb, Mar and Apr there were no activities i input "nothing reported" and it should show 0, but when we talk about future months like June, July the cells are blank (no text and no figures) it should show blank cell without zero. As soon as data entered, if numbers it will count cells, and if text only it will show 0. Is it possible? Please, find attached excel file with an example. Thanks a lot!

7. ## Re: COUNT cells and IF ALL cells are blank do not display 0

Any help please on the above? Cheers!

8. ## Re: COUNT cells and IF ALL cells are blank do not display 0

In the sample file there are 3 rows allotted for each month. Will there always be only 3 rows of data per month?

9. ## Re: COUNT cells and IF ALL cells are blank do not display 0

No it may go up to 5 maximum. Thanks!!

10. ## Re: COUNT cells and IF ALL cells are blank do not display 0

Try this...

=IF(COUNTA(B3:B5)=0,"",IF(COUNT(B3:B5),COUNT(B3:B5),0))

11. ## Re: COUNT cells and IF ALL cells are blank do not display 0

It works fantastic! Thanks a lot!!!! Highly appreciated!

12. ## Re: COUNT cells and IF ALL cells are blank do not display 0

You're welcome. Thanks for the feedback!

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1