Subtotal that excludes duplicates

[MrsFraser07]

Hi there,

I came up with the following formula (thanks to alot of Googling!):

{=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET($H$4,ROW(H4:H1048576)-ROW($H$4),,1)),IF(H4:H1048576<>"",MATCH("~"&H4:H1048576,H4:H1048576&"",0))),ROW(H4:H1048576)-ROW(H4)+1),1))}

It works, but it is very slow. The data in column H is text. I will be filtering in a different column in my spreadsheet and I want a total of all unique values in column H. Wondering if there is a more efficient way to do this? To help speed things up? I realize it might help if I didn't have all the way to H1048576, I just do this out of habit so I don't have to change my formulas if I add more rows.

Many thanks!
Shari

 

[jasonb75]

I realize it might help if I didn't have all the way to H1048576

You've answered your own question.

Think about it realistically, are you ever going to add over 1 million rows of data?

 

[MrsFraser07]

Oh wow!!! I just tried it, reduced my formula to H10000 and it is way faster! Didn't think it would make that big of a difference. Thanks!

Just curious though.....I don't even know what some of this formula means and I would like to learn.

What is the MATCH("~"&H4:H1048576,H4:H1048576&"",0)) part saying?

 

[jasonb75]

No idea, best to ask whoever you got it from.

Typically "~" is used to identify "*" or "?" as actual characters instead of wildcards, beyond that I don't think it has any purpose.