How to return the number of text cells in a filtered range

[Adamlee]

Hi All,

I am looking to calculate the number of cells that contain "PASS" which is mixed amongst other cells. I can use COUNTIF, but can i apply this formulae to visable cells only?

I have added subtotal103, however i think this will only work if the other cells are blank.

Please help.

Thanks,

Adam

[Norie]

Adam

SUBTOTAL should work with filtered data, and I'm pretty sure it has nothing to do with blank cells.

How exactly did you try it?

[Adamlee]

Hi Norie,

Thanks for the reply.

I used the following but its returning an error. i presume it is because i am double counting. Apart from countif, i am not sure how to count text cells.

=SUBTOTAL(102, COUNTIF(N26:N72, "Pass"))

Thanks,

Adam

[Norie]

How have you filtered?

[Adamlee]

Pass and fail is accumulated by meeting certain criteria which is found by looking at several cells. The problem arises when i want to look at how many passed and failed when one specific set of results is held constant.

[kvsrinivasamurthy]

Pl see file with UDF subCountIF

Please Login or Register  to view this content.

[Adamlee]

Hi,

I have attached an example on sheet 2.

If i wanted to look at purple items only or maybe yellow.

Thanks,

Adam

[Adamlee]

Hi KVS,

How do i add that new function to Excel, It looks to work fine.

[Adamlee]

Anybody please ?? =(

[Norie]

Try this it will count the Pass in column K where column C is Purple.

Please Login or Register  to view this content.

Same but using COUNTIFS.

Please Login or Register  to view this content.

[bebo021999]

How about this with helper column?

[Adamlee]

Thanks Norie,

What i would like to achieve is be able to change the first filter and it will always give me the %passed. The attached excel sheet is very basic. My current sheet will contain alot of variables.

[Jonmo1]

I prefer to put the subtotal in it's own column.
Particularly if there will be multiple formulas that need to work with only visible cells.

So in an available column, say M, Put
M2: =SUBTOTAL(103,N2)
and fill down.

Now use
COUNTIFS(M26:M72,1,N26:N72, "Pass")

[Adamlee]

Hi Jonmo1,

Can you please have a look at my excel and see what im doing wrong. I understand it probably looks stupid as i am selecting boxs with nothing in it.

I was thinking about having a hidden coloum and in that column returning a 0 for a fail and a 1 for a pass. Then using subtotal can count the 1s. Is that possible? Maybe an IF function?

[vlady]

in your yellow cell K1

=SUMPRODUCT(--(K5:K100="Pass"),SUBTOTAL(3,OFFSET(K5,ROW(K5:K100)-ROW(K5),0)))

note the range is just until K100 on the sample above

[Adamlee]

I dont quite understand the coding but it works perfectly.

Thanks very much for your help =)

[vlady]

Nice. Your welcome.

regards,
vlady

[Adamlee]

Hmmm i double posted by acciedent

[Adamlee]

Hi Vlad,

I have added an extra cell to the excel sheet which i am trying to figure out. I have uploaded it in a new thread. If you get the chance could you please take a look.

Thanks,

Adam

[Jonmo1]

Hi Jonmo1,

Can you please have a look at my excel and see what im doing wrong.

Here's an example using the method I described in post #13
EFAdamlee.xlsx

[Adamlee]

Hi Jonmo,

You know the way you have the 1 being produced when the first column reads a color. Is it possible to use the same formula without having a color?

eg. It wil return a 1 for any text?

[Jonmo1]

Yes, the formula I put in column L will return a 1 if there is ANY value in column C AND that cell is Visible after autofilter.
It will be 0 if the cell in column C is empty OR Not Visible after autofilter.

[Adamlee]

That is exactly what i wanted. I got confused because it said color in the fomula but i presume that it is because its the name of the column?

Thanks so much.

[Jonmo1]

You're welcome.

but i presume that it is because its the name of the column?

That's correct.