Using COUNTIF with Filters

[ravenrt]

Hi,

I have a spreadsheet which has a list of various information in. Column A has customer name and then column b has the date an activity was completed by. At the bottom I have got a list of all the customers names and then used COUNTIF to calculate how many times they appear in the list. What I need to do is when the information is filtered between two data ranges it will only count the customers between that data range e.g. what is then displayed and not all the information like it is currently doing.

[Domenic]

Try the following...

=SUMPRODUCT(SUBTOTAL(3,OFFSET(A2:A100,ROW(A2:A100)-MIN(ROW(A2:A100)),0,1)),--(A2:A100="CustomerName"))

If by 'filtered between two data ranges' you mean filtered between two dates in Column B, you can use the following formula without the need to apply the filter...

=SUMPRODUCT(--(A2:A100="CustomerName"),--(B2:B100>="1/1/2005"+0),--(B2:B100<="1/31/2005"+0))

OR

=SUMPRODUCT(--(A2:A100="CustomerName"),--(B2:B100>=C1),--(B2:B100<=D1))

...where C1 contains the start date, and D1 the end date.

Hope this helps!

Hi,

I have a spreadsheet which has a list of various information in. Column A has customer name and then column b has the date an activity was completed by. At the bottom I have got a list of all the customers names and then used COUNTIF to calculate how many times they appear in the list. What I need to do is when the information is filtered between two data ranges it will only count the customers between that data range e.g. what is then displayed and not all the information like it is currently doing.