Forum Discussion
Solved
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...
- 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.
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.
=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.
- Yes, it works. Thanks so much!
