SOLVED

Sumproduct for Dynamic ranges

%3CLINGO-SUB%20id%3D%22lingo-sub-1968125%22%20slang%3D%22en-US%22%3ESumproduct%20for%20Dynamic%20ranges%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1968125%22%20slang%3D%22en-US%22%3EDear%20All%2C%3CBR%20%2F%3EIn%20the%20attached%20excel%20file%20there%20are%20two%20drop-down%20box%2C%20I%20want%20to%20have%20the%20sum%20total%20expenses%20in%20Q%20column%20based%20on%20the%20month%20selection%20by%20the%20user%20from%20drop-down%20box.%3CBR%20%2F%3EPlease%20Help.%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1968125%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-1968168%22%20slang%3D%22en-US%22%3ERE%3A%20Sumproduct%20for%20Dynamic%20ranges%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1968168%22%20slang%3D%22en-US%22%3EIt%20appears%20the%20file%20did%20not%20upload%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1968488%22%20slang%3D%22en-US%22%3ERe%3A%20Sumproduct%20for%20Dynamic%20ranges%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1968488%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F767899%22%20target%3D%22_blank%22%3E%40SubhasisB%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSUM(XLOOKUP(O3%2C%24C%242%3A%24N%242%2C%24C3%3A%24N3)%3AXLOOKUP(P3%2C%24C%242%3A%24N%242%2C%24C3%3A%24N3))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eor%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSUM(INDEX(C4%3AN4%2CMATCH(O4%2C%24C%242%3A%24N%242%2C0))%3AINDEX(C4%3AN4%2CMATCH(P4%2C%24C%242%3A%24N%242%2C0)))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20961px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F238785i4B5171DDB1C8BDBB%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor
Dear All,
In the attached excel file there are two drop-down box, I want to have the sum total expenses in Q column based on the month selection by the user from drop-down box.
Please Help.
3 Replies
It appears the file did not upload?
best response confirmed by SubhasisB (Occasional Contributor)
Solution

@SubhasisB 

That could be

=SUM(XLOOKUP(O3,$C$2:$N$2,$C3:$N3):XLOOKUP(P3,$C$2:$N$2,$C3:$N3))

or

=SUM(INDEX(C4:N4,MATCH(O4,$C$2:$N$2,0)):INDEX(C4:N4,MATCH(P4,$C$2:$N$2,0)))

image.png

@JMB17 Please find attached the file.