Count only visible cells after filtering

0
votes

I have been trying to do an excel formula but unable to find the right combination.

Aim: I want to COUNTIFS cells that are only visible after applying filters to the original dataset.

Current/Wrong output: 38

Correct output: 15

Here is the current formula:

=COUNTIFS(INDEX('Current Month'!$A:AJ,0,MATCH("Dryness + Absorbency",'Current Month'!$A$1:$AJ$1,0)),"POSITIVE")

I have already tried the subtotal but the values are wrong. Secondly, the subtotal function displays error with index/match function.

Here is what i tried:

=COUNTA(103,INDEX('Current Month'!$A:AJ,0,MATCH("Dryness + Absorbency",'Current Month'!$A$1:$AJ$1,0)),"POSITIVE")

I also tried replacing the COUNTA with SUBTOTAL but it shows error.

Hope someone could advice me on what syntax i can use to get the visible cells count and how i could incorporate it into my current formula. Thank you

2
excelexcel-formulavba
There is a clever way of counting only visible rows here exceljet.net/formula/count-visible-rows-only-with-criteria - Tom Sharpe
Hi, Thanks for the help. I will try using the formula provided in the link but it seems to be a little difficult with index match to be incorporated into the formula. - Faheera

2 Answers

0
votes

Use the subtotal function. It will adjust the return value based on filters.

More info here on how to make it conditional: https://www.mrexcel.com/forum/excel-questions/63754-subtotal-if.html

0
votes

Well this is what you get if you substitute your index function into the standard formula that I mentioned

=SUMPRODUCT((INDEX('Current month'!$A1:AJ10,0,MATCH("Dryness + Absorbency",'Current month'!$A$1:$AJ$1,0))=C10)*(SUBTOTAL(103,OFFSET(INDEX('Current month'!$A1:AJ10,1,MATCH("Dryness + Absorbency",'Current month'!$A$1:$AJ$1,0)),ROW(INDEX('Current month'!$A1:AJ10,0,MATCH("Dryness + Absorbency",'Current month'!$A$1:$AJ$1,0)))-MIN(ROW(INDEX('Current month'!$A1:AJ10,0,MATCH("Dryness + Absorbency",'Current month'!$A$1:$AJ$1,0)))),0))))

Yes it is a bit long but does work.

I have changed full-column references to first ten rows in order to test it. You can use full-column references but it is a bit slow

=SUMPRODUCT((INDEX('Current month'!A:AJ,0,MATCH("Dryness + Absorbency",'Current month'!A1:AJ1,0))=C10)*(SUBTOTAL(103,OFFSET(INDEX('Current month'!A:AJ,1,MATCH("Dryness + Absorbency",'Current month'!A1:AJ1,0)),ROW(INDEX('Current month'!A:AJ,0,MATCH("Dryness + Absorbency",'Current month'!A1:AJ1,0)))-MIN(ROW(INDEX('Current month'!A:AJ,0,MATCH("Dryness + Absorbency",'Current month'!A1:AJ1)))),0))))

I had to change the row parameter in the INDEX statement following the OFFSET from 0 to 1 to get just a single cell reference to OFFSET from.

My test data looks like this and gives a count of 5 positives unfiltered, 2 positives when filtered for an 'x' in column E.

enter image description here