MrsFraser07
New Member
- Joined
- Aug 16, 2017
- Messages
- 44
- Office Version
- 365
- Platform
- Windows
- MacOS
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
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