Forum Discussion
kthersh
Feb 14, 2023Copper Contributor
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...
kthersh
Feb 14, 2023Copper Contributor
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
=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
Feb 15, 2023Gold Contributor
=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?
- kthershFeb 16, 2023Copper ContributorThanks, but I do want it to count only the times a unique ID shows up in a row