Forum Discussion
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...
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))