Count Visible Cells Only

[howard]

I have formula in Col B below thast returns a blank if an error is returned

=IFERROR(LEFT(A1, SEARCH("VAT", A1) - 2),"")

I need a fortmula to count only the visivle items, but my formula is count the non-visible item, where the formula above generates a blank if thee is an error. I want these cells to be ignored

=COUNTIF(B1:B33,"<>")

Kinddly amend my formula

 

[Alex Blakenburg]

Your heading is a bit misleading. Visible cells normally refers to having filtered rows so that they are not visible which doesn't seem to be the case here.

Your first formula seems to be stripping out the VAT rate from the text but you are left with a text value instead of a numeric value. The below gives you an alternative in Column C as well as a count non-blanks formula.
Note: CountIfs, SumIfs etc only treat empty cells as meeting the criteria "<>" and do not consider "" as being empty.

Book1
	A	B	C	D	E	F	G
1	10 VAT	10	10
2	20 VAT	20	20			Count Col B	Count Col C
3					Count -->	3	3
4	50 VAT	50	50
5
6
7
Sheet1

Cell Formulas
Range	Formula
B1:B7	B1	=IFERROR(LEFT(A1, SEARCH("VAT", A1) - 2),"")
C1:C7	C1	=IFERROR(VALUE(LEFT(A1, SEARCH("VAT", A1) - 2)),"")
F3	F3	=COUNTA(FILTER($B$1:$B$33,$B$1:$B$33<>""))
G3	G3	=COUNTA(FILTER($C$1:$C$33,$C$1:$C$33<>""))

 

[howard]

Thanks Alex. Code works 100%

 

[Alex Blakenburg]

Thanks for letting me know. Glad I could help.

 

[Peter_SSs]

I don't know what the actual data is like, but a simpler formula for cell G3 in Alex's set-up would be

=COUNT(C1:C33)

 

[howard]

Thanks for your input Peter, but your formula returns zero in this instance -see link below

Count Data.xlsx

Shared with Dropbox

www.dropbox.com

 

[Peter_SSs]

Thanks for your input Peter, but your formula returns zero in this instance -see link below

That is because my formula was prefixed with ..

a simpler formula for cell G3 in Alex's set-up would be

In Alex's set-up, the formula in G3 was counting values in column C and his values in column C were numbers. Yours are not - but we didn't have that information at the time.
With over 6,000 posts I would have thought you would understand the importance of sample data up-front so that helpers know what they are actually dealing with.

 

[howard]

You are 100% correct Peter. I should have posted samole data in my 1st post and apologise for not doing so

 

[Peter_SSs]

Wouldn't this formula produce the same result without the need for column B?

=COUNTIF(A1:A33,"*??VAT*")

 

[howard]

Thanks Peter. You are 100% no need for Column B , using this formula