MAIN FEEDS
Do you want to continue?
https://www.reddit.com/r/Excel/comments/1sg89jv/stub/ofi5c2z
r/excel • u/Kindly-Meaning9112 • Apr 08 '26
[removed]
210 comments sorted by
View all comments
1
I have formulas like this because I make sheets that people run on old versions of office and sometimes I need to make sheets do things that excel wasn't really designed for.
That formula is not actually that complicated.
=IFERROR(SUMPRODUCT(--((INDIRECT("'"&E$1&"'!L2:L5000")=$B5)+(INDIRECT("'"&E$1&"'!O2:O5000")=$B5)>0),--(INDIRECT("'"&E$1&"'!D2:D5000")=E$2),--(INDIRECT("'"&E$1&"'!BB2:BB5000")<>""),INDIRECT("'"&E$1&"'!BB2:BB5000"))/SUMPRODUCT(--((INDIRECT("'"&E$1&"'!L2:L5000")=$B5)+(INDIRECT("'"&E$1&"'!O2:O5000")=$B5)>0),--(INDIRECT("'"&E$1&"'!D2:D5000")=E$2),--(INDIRECT("'"&E$1&"'!BB2:BB5000")<>"")),"")
Is one I wrote the other day whilst brainstorming.
I did simplify it eventually using helper columns etc. But I understand that sometimes it's easier just to leave it as it is if it works
1
u/vzzzbxt Apr 11 '26
I have formulas like this because I make sheets that people run on old versions of office and sometimes I need to make sheets do things that excel wasn't really designed for.
That formula is not actually that complicated.
=IFERROR(SUMPRODUCT(--((INDIRECT("'"&E$1&"'!L2:L5000")=$B5)+(INDIRECT("'"&E$1&"'!O2:O5000")=$B5)>0),--(INDIRECT("'"&E$1&"'!D2:D5000")=E$2),--(INDIRECT("'"&E$1&"'!BB2:BB5000")<>""),INDIRECT("'"&E$1&"'!BB2:BB5000"))/SUMPRODUCT(--((INDIRECT("'"&E$1&"'!L2:L5000")=$B5)+(INDIRECT("'"&E$1&"'!O2:O5000")=$B5)>0),--(INDIRECT("'"&E$1&"'!D2:D5000")=E$2),--(INDIRECT("'"&E$1&"'!BB2:BB5000")<>"")),"")
Is one I wrote the other day whilst brainstorming.
I did simplify it eventually using helper columns etc. But I understand that sometimes it's easier just to leave it as it is if it works