Having difficulties using COUNTIF with filtered columns

[stevee22001]

Having difficulties using COUNTIF with filtered columns, column A not really
relevant to this request, column B is either yes or no, column C & D are not
relevant to this request either, column E is either aa, bb, cc or dd.

I am using =COUNTIF(E4:E26, "aa") to count the number of times aa appears,
this is getting me a figure, but when I filter column B to show with the
yes’s or no’s the figure doesn’t change.

Thanks in advance for any help!!

 

[Mike H]

Hi,

Try this

=SUMPRODUCT(SUBTOTAL(3,OFFSET(E4:E26,ROW(E4:E26)-MIN(ROW(E4:E26)),,1))*(E4:E26="aa"))

Mike

 

[Teddy]

I use the following solution in a similar situation.
Unfilter your data - then go to the very end and leave a blank row then use
the =subtotal(3,e4:e26) 3=counta Actually found this solution on this
discussion somewhere and works perfectly for me.

Then turn on the filter and apply filters to your data as you wish. This
figure will always update automatically showing you the total number of
records being viewed.

Hope this helps.
Teddy

 

[Teddy]

Oops meant to write the formula to include ALL rows of data so it would be
something like =subtotal(3,e2:e1245)
Cheers.