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

MVP

Feb 14, 2023

If you don't have Microsoft 365 or Office 2021, but an older version, confirm the following formula by pressing Ctrl+Shift+Enter:

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

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
- HansVogelaar
  MVP
  Feb 14, 2023
  kthersh
  
  The new range is larger than the old one, so it is not surprising if there are more unique values.

Resources