Forum Discussion

Copper Contributor

Feb 14, 2023

Adjust a formula to ignore hidden/filtered rows of data

I have a formula already setup to calculate the total occurrences of a unique ID in a column. =SUM(IF(ISNUMBER(A2:A2719)*COUNTIF(A2:A2719,A2:A2719)=1,1,0)) But when I have a filter set on a t...

Formulas and Functions

kthersh

Copper Contributor

Feb 14, 2023

Thank you for the fast response, I'm going to use a different cell range for my test, which I updated in the formula below from the copy of yours, but the calculation does not seem to correct

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A2,ROW(A2:A5928)-ROW(A2),,1)), IF(A2:A5928<>"",MATCH("~"&A2:A5928,A2:A5928&"",0))),ROW(A2:A5928)-ROW(A2)+1),1))

If I use the previous formula, I get 4505, if I use your new one I get 5138 on the same set of data

OliverScheurich

Gold Contributor

Feb 15, 2023

kthersh

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A2,ROW(A2:A5928)-ROW(A2),,1)), IF(A2:A5928<>"",MATCH("~"&A2:A5928,A2:A5928&"",0))),ROW(A2:A5928)-ROW(A2)+1)=1,1))

Do you want to count the IDs that appear only one time in your data instead of counting the number of unique IDs? I've added "=1" to the solution by HansVogelaar and it returns the expected result in my sample sheet. Does this formula return 4505 in your sheet?

kthersh
Copper Contributor
Feb 16, 2023
Thanks, but I do want it to count only the times a unique ID shows up in a row