autofiler and formula

[erky]

I have an excel sheet with lots of data and I have autofilter enabled. When I add this formula to my sheet, and I click on the autofilter arrow to filter the data and when it changes to that specific data my formula is gone. It only stays when I am not filtering the data. Is there any way that I can make my formula stay on the sheet regardless if I filter the data or not. Thanks for all your help. I hope I explained this clearly.

 

[T. Valko]

I have an excel sheet with lots of data and I have autofilter enabled. When I add this formula to my sheet, and I click on the autofilter arrow to filter the data and when it changes to that specific data my formula is gone. It only stays when I am not filtering the data. Is there any way that I can make my formula stay on the sheet regardless if I filter the data or not. Thanks for all your help. I hope I explained this clearly.

Put the formula at the top of the sheet above the filtered range.

 

[erky]

Ok, that keeps it but now it is not doing the formula.

=countif(c6:c8888,"T")

That is the forumla, but when I use the autofilter it wont count now because the row starts at C200, etc. now

Pretty much I want it to go down column C and count how many T's are showing. When I click the filter arrow and the data changes I want it to just recalculate the info just on that filtered data, not what I have in the entire worksheet. Maybe I should use a different formula? THanks for the quick reply already

 

[T. Valko]

Ok, that keeps it but now it is not doing the formula.

=countif(c6:c8888,"T")

That is the forumla, but when I use the autofilter it wont count now because the row starts at C200, etc. now.

Thanks for the quick reply.

Try this...

=SUMPRODUCT(SUBTOTAL(3,OFFSET(C6:C8888,ROW(C6:C8888)-ROW(C6),0,1)),--(C6:C8888="T"))

 

[erky]

It is like still counting all the data in the entire sheet, not just what I have filtered

 

[erky]

That formula you gave me worked PERFECTLY. THANK YOU SOOOO MUCH!!

 

[erky]

1 last question, I have it counting how many T's but how can I have it add the total number also of T's, P's, O's, and also S's. Thanks again for the help

 

[T. Valko]

That formula you gave me worked PERFECTLY. THANK YOU SOOOO MUCH!!

You're welcome. Thanks for the feedback!

 

[T. Valko]

1 last question, I have it counting how many T's but how can I have it add the total number also of T's, P's, O's, and also S's. Thanks again for the help

Use cells to hold those criteria:

• A1 = T
• B1 = P
• C1 = O
• D1 = S

Then the formula becomes:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(C6:C8888,ROW(C6:C8888)-ROW(C6),0,1)),--(ISNUMBER(MATCH(C6:C8888,A1:D1,0))))

 

[erky]

Sweet, thanks. Again, thanks for all the help, I really appreciate it.