Getting =COUNTIFS to change when results are filtered - Please help!

Try this: =SUMPRODUCT(SUBTOTAL(3,OFFSET(Input!$F$2,ROW(Input!$F$2:$F$45653)-ROW(Input!$F$2),0,1)),--(Input!$F$2:$F$45653="promotor"),--(Input!$X$2:$X$45653="*service*"))

Hello Mattmcging. Welcome to the forum.

Unless PaulM100's answer solves for you please upload a sample workbook that is representative of what you have to work with. Also please show results of what you are trying to do.

Hi, thanks for the swift reply but I'm not sure the solution works.

I've trimmed some data (about 45.5k rows!) for a sample workbook of 20 rows and uploaded here to help show what I mean. The data is related to customer comments and reviews, but they have been anonymized with names removed etc.

On the "responses by type" table on the graphs sheet, cells B9:D9 feature your solution, but they all show a value of zero, whether I filter results or not. I have left my original working countifs in all other cells in the table to show how I want it to work, but obviously thecountifs don't change when the results on the !input sheet are filtered.

The main filter I would use would be by postcode, so a specific region could be looked at in more detail.

Please let me know if any further detail required.

Thanks!

If you are filtering column Q, use OFFSET column Q:

B2
=SUMPRODUCT(SUBTOTAL(3,OFFSET(Input!$Q$2:$Q$45653,ROW(Input!$Q$2:$Q$45653)-MIN(ROW(Input!$Q$2:$Q$45653)),,1)),ISNUMBER(SEARCH(A2,Input!$F$2:$F$45653))+0)

B8:
=SUMPRODUCT(SUBTOTAL(3,OFFSET(Input!$Q$2,ROW(Input!$Q$2:$Q$45653)-ROW(Input!$Q$2),0,1))*(Input!$F$2:$F$45653=B$7)*ISNUMBER(SEARCH("postie",Input!$X$2:$X$45653)))

Copy down and change "postie" to relevant text criteria

Drag accross