Excel Countif of filtered data

[kashif.special2005]

Hi,

Could we use COUNTIF function of filtered data, like we use subtotal for sum and average etc.,

Scenario:-

like I have columns Area in column A, and Sales in column B, and when I filter in column A like "north" then I want to count sales, that how many sales are greater than 500, and when I changes filter from "north" to "south" the result should change accordingly, like subtotal.

Thanks
Kashif

 

[helpexce]

You can create a data validation for the area and then apply formula sumif for the entire sales. The formula is:
=SUMIF(E3:E7,G3,F3:F7)
E3:E7 is area
G3 is area for which you wanna get sales (you can have data validation in G3 instead of filtering entire data time and again)
F3:F7 is sales

 

[Peter_SSs]

Try this, adjusting ranges to suit your data.

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B14,ROW(B2:B14)-ROW(B$2),,1))*(B2:B14>500))

 

[kashif.special2005]

Hi Peter_SSs/helpexce,

Thanks for reply.

Peter_SSs it is working fine, thanks for your time


Thanks
Kashif

 

[Peter_SSs]

Peter_SSs it is working fine, thanks for your time

Glad to help. Thanks for letting us know.