excel countifs only filtered data

[bizzbuzz05]

Hi,

I have a Countifs function but I need it only to do filtered data. I have tried looking up how to do the "SUMPRODUCT" but cant get it to work.

Here is the Countifs =COUNTIFS(A7:A8000,"=Female",H7:H8000,"=Always").
How would I change it to the SUMPRODUCT or another function to count only filtered data?

Thanks
Bizzbuzz05

 

[Aladin Akyurek]

Try...

=SUMPRODUCT(SUBTOTAL(3,OFFSET(A7,ROW(A7:A8000)-ROW(A7),0,1)),--(A7:A8000="Female"),--(H7:H8000="Always"))

 

[bizzbuzz05]

Thanks for reply and helping.

It only shows 0.

Try...

=SUMPRODUCT(SUBTOTAL(3,OFFSET(A7,ROW(A7:A8000)-ROW(A7),0,1)),--(A7:A8000="Female"),--(H7:H8000="Always"))

 

[Aladin Akyurek]

Thanks for reply and helping.

It only shows 0.

I assume that you have headers in row 6 and you apply filters on one or some of these headers (autofilter).

If so, the suggested formulashould work.


If you do filtering manually, try...

=SUMPRODUCT(SUBTOTAL(103,OFFSET(A7,ROW(A7:A8000)-ROW(A7),0,1)),--(A7:A8000="Female"),--(H7:H8000="Always"))

 

[bizzbuzz05]

Om my god!! it works.. thank you, thank you, thank you.

Ive been working on it all morning. thank you again.

I assume that you have headers in row 6 and you apply filters on one or some of these headers (autofilter).

If so, the suggested formulashould work.


If you do filtering manually, try...

=SUMPRODUCT(SUBTOTAL(103,OFFSET(A7,ROW(A7:A8000)-ROW(A7),0,1)),--(A7:A8000="Female"),--(H7:H8000="Always"))

 

[Aladin Akyurek]

You are welcome.

 

[MrTwister85]

Hello Aladin,
Im trying to count number of cells that do not equal 0 in any column with applied filter (say i applied filter in column A and i want the count in column B (only for visible rows orfcourse). What would be the formula?

 

[Sufiyan97]

Hi & Welcome to MrExcel.

Try

=SUMPRODUCT(SUBTOTAL(103,OFFSET(B2,ROW(B2:B100)-ROW(B2),0,1)),--(B2:B100<>0))

 

[MrTwister85]

Hello Sufiyan, its giving me #N/A error

 

[Sufiyan97]

Can you post some sample data using below xl2bb link