Using COUNTIFS Filtered Data

[CraigMcKee]

Hello,

Is it possible to user COUNTIFS on filtered data? At the moment my source data is filtered but the COUNTIFS is returning everything.

Either that or can I dynamically copy all of the filtered data to a new sheet so this only displays the filtered data then I could get my COUNTIFS to look there?

[PaulM100]

You can look into a SUMPRODUCT+SUBTOTAL formula

[CraigMcKee]

Thanks Paul, I'd seen some examples of that but none using COUNTIFS so didn't know if it was possible.

[PaulM100]

Upload a sample of your data so we can provide you a solution.

[CraigMcKee]

Here you go. There is the sheet with the slicer filter on and then on the Question 1 sheet I have used a COUNTIFS to try and summarise but of course it includes everything, not just the filtered data.

[AliGW]

Why the .zip file? Please attach a file in normal .xls or .xlsx format - it only needs just enough data to illustrate the problem/requirement.

[johnmpl]

Hi, to all!

An option could be (Partial result in Question 1, for example):
=SUMPRODUCT(SUBTOTAL(103,OFFSET(G5,ROW(G5:G19)-ROW(G5),)),N(G5:G19="Partial"))

Blessings!

[CraigMcKee]

Thanks John, I'll take a look and read through the documents for the SUMPRODUCT / SUBTOTAL / OFFSET functions as I am keen to understand.

[CraigMcKee]

John, would that only result in responses from BOYS that were "Partial" though?

[PaulM100]

You can use John's formula like this:

=SUMPRODUCT(SUBTOTAL(103,OFFSET(G5,ROW(G5:G19)-ROW(G5),)),N(G5:G19=$F22))

$F22 instead of Partial

Capture.JPG

[CraigMcKee]

Sorry, Paul. What I'm saying is though the data has been filtered by School name so there will be boys and girls in the list. I only want to then count BOYS and PARTIAL if that makes sense so I still need a COUNTIFS equivalent.

[CraigMcKee]

=SUMPRODUCT(SUBTOTAL(3,OFFSET(E:E,ROW(E:E)-MIN(ROW(E:E)),,1)),--(F:F=M1),--(G:G=N1))

Got it - thanks for all your help. I'm still not sure what the OFFSET does but it works!

[PaulM100]

Or you can try this:

=SUMPRODUCT(SUBTOTAL(103,OFFSET(E6,ROW(G6:G19)-ROW(E6),0)),(G6:G19="Partial")*(F6:F19="Boy"))