5

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?

Lance Roberts
  • 22,383
  • 32
  • 112
  • 130
Mitch
  • 2,471
  • 8
  • 38
  • 46

1 Answers1

3

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.

jonsca
  • 10,218
  • 26
  • 54
  • 62
  • Thanks jonsca, but I had tried that approach =SUBTOTAL(9, IF(FREQUENCY(MATCH(C4:C9,C4:C9,0),MATCH(C4:C9,C4:C9,0))>0,1)) – Mitch Aug 24 '11 at 14:11
  • @Mitch Yes, the subtotal must be used with one of those functions listed. Why do you need the `match` formula in there? Just click on the label row and select which elements you want visible. – jonsca Aug 24 '11 at 14:14
  • I think the problem is that simply replacing =SUM with =SUBTOTAL(9, is ok when you have a range specified ie =(SUBTOTAL(9, C4:C9), but not when it contains a formula ie =SUBTOTAL(9, IF(FREQUENCY(MATCH(C4:C9,C4:C9,0),MATCH(C4:C9,C4:C9,0))>0,1)). Maybe it needs to be totally refactored to use SUBTOTAL? – Mitch Aug 24 '11 at 14:17
  • @Mitch I think I see what you are saying. I'm not sure if there's a shorthand way to do it, but you could say if the statement is true, put a 1 in the adjacent cell and then sum those adjacent cells using subtotal. – jonsca Aug 24 '11 at 14:22