Change Excel formula to total only visible cells

5
votes

I'm using the following formula to create a total of unique values

=SUM(IF(FREQUENCY(MATCH(C4:C9,C4:C9,0),MATCH(C4:C9,C4:C9,0))>0,1))

However, I'm now using an autofilter, so I now need it to sum only the visible rows. I've read that I need to use SUBTOTAL instead of SUM, but I'm not sure how to change the formula above correcly.

Any ideas?

1
excelworksheet-functionsubtotal

1 Answers

3
votes

Use =SUBTOTAL(9,C4:C9), where 9 is the function number of sum ( if you're typing this into the sheet, the intellisense dialog will pop up and you can select it without having to remember the function number).

I don't remember where it is in 2003, but I know that in 2007 or later, if your filtered column has a header, select the cells (including the header), and click the subtotal box on the Data tab.

Edit: I think you are looking for a COUNTIF instead: =COUNTIF(C4:C9,"=FREQUENCY(etc.)>0"). You may need an array formula with that if you are using MATCH.