SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-3089326%22%20slang%3D%22en-US%22%3ECan%20someone%20help%20me%20covert%20this%20formula%20for%20visible%20rows%20only%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3089326%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFirst%20post%20here.%20I%20need%20help%20converting%20the%20formula%20below%20to%20only%20calculate%20for%20visible%20rows%20only%20in%20a%20filtered%20list.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3D(COUNTIFS(Data!D%3AD%2C%22%3DH%22%2CData!AK%3AAK%2C%22%3DMasters%22)*3%2BCOUNTIFS(Data!D%3AD%2C%22%3DH%22%2CData!AK%3AAK%2C%22%3DMeets%22)*2%2BCOUNTIFS(Data!D%3AD%2C%22%3DH%22%2CData!AK%3AAK%2C%22%3DApproaches%22))%2FF76%2F3%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3089326%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3089405%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20someone%20help%20me%20covert%20this%20formula%20for%20visible%20rows%20only%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3089405%22%20slang%3D%22en-US%22%3EPerhaps%20this%20will%20work%20for%20you%3A%3CBR%20%2F%3E%3DSUMPRODUCT((Data!D%3AD%3D%22H%22)*(Data!AK%3AAK%3D%7B%22Masters%22%2C%22Meets%22%2C%22Approaches%22%7D)*SUBTOTAL(3%2COFFSET(INDEX(Data!D%3AD%2C1)%2CROW(INDIRECT(%221%3A%22%26amp%3BROWS(Data!D%3AD)))-1%2C0%2C1%2C1))*%7B3%2C2%2C1%7D)%2FF76%2F3%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20you%20have%20office%20365%2C%20I%20would%20replace%20the%20Row(Indirect(...))%20with%20sequence%20function.%20I%20believe%20it%20would%20be%3A%3CBR%20%2F%3ESUMPRODUCT((Data!D%3AD%3D%22H%22)*(Data!AK%3AAK%3D%7B%22Masters%22%2C%22Meets%22%2C%22Approaches%22%7D)*SUBTOTAL(3%2COFFSET(INDEX(Data!D%3AD%2C1)%2CSequence(ROWS(Data!D%3AD))-1%2C0%2C1%2C1))*%7B3%2C2%2C1%7D)%2FF76%2F3%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20it%20is%20possible%2C%20you%20might%20consider%20putting%20your%20data%20into%20a%20structured%20table.%20Then%2C%20you%20could%20use%20structured%20table%20references%20in%20your%20formula%2C%20%2C%20which%20will%20update%20as%20your%20table%20size%20changes%2C%20instead%20of%20entire%20column%20references%2C%20which%20will%20likely%20noticeably%20lag.%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3096012%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20someone%20help%20me%20covert%20this%20formula%20for%20visible%20rows%20only%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3096012%22%20slang%3D%22en-US%22%3EYes%2C%20it%20works.%20Thanks%20so%20much!%3C%2FLINGO-BODY%3E
New Contributor

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

 

Thanks in advance!

2 Replies
best response confirmed by MatrixED (New Contributor)
Solution
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.
Yes, it works. Thanks so much!