SOLVED

New Contributor

# Can someone help me covert this formula for visible rows only?

Hello,

First post here. I need help converting the formula below to only calculate for visible rows only in a filtered list.

=(COUNTIFS(Data!D:D,"=H",Data!AK:AK,"=Masters")*3+COUNTIFS(Data!D:D,"=H",Data!AK:AK,"=Meets")*2+COUNTIFS(Data!D:D,"=H",Data!AK:AK,"=Approaches"))/F76/3

2 Replies
best response confirmed by MatrixED (New Contributor)
Solution

# Re: Can someone help me covert this formula for visible rows only?

Perhaps this will work for you:
=SUMPRODUCT((Data!D:D="H")*(Data!AK:AK={"Masters","Meets","Approaches"})*SUBTOTAL(3,OFFSET(INDEX(Data!D:D,1),ROW(INDIRECT("1:"&ROWS(Data!D:D)))-1,0,1,1))*{3,2,1})/F76/3

If you have office 365, I would replace the Row(Indirect(...)) with sequence function. I believe it would be:
SUMPRODUCT((Data!D:D="H")*(Data!AK:AK={"Masters","Meets","Approaches"})*SUBTOTAL(3,OFFSET(INDEX(Data!D:D,1),Sequence(ROWS(Data!D:D))-1,0,1,1))*{3,2,1})/F76/3

If it is possible, you might consider putting your data into a structured table. Then, you could use structured table references in your formula, , which will update as your table size changes, instead of entire column references, which will likely noticeably lag.

# Re: Can someone help me covert this formula for visible rows only?

Yes, it works. Thanks so much!