SOLVED

SUMPRODUCT with multiple ranges?

%3CLINGO-SUB%20id%3D%22lingo-sub-3005744%22%20slang%3D%22en-US%22%3ESUMPRODUCT%20with%20multiple%20ranges%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3005744%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20%22merge%22%20two%20SUMPRODUCT%20calculations%20in%20one%20formula%20but%20get%20an%20N%2FA%20error.%20Both%20SUMPRODUCT%20formulas%20work%20on%20their%20own%20but%20not%20when%20I%20add%20them%20together.%20What%20am%20I%20doing%20wrong%3F%20Or%20can't%20it%20be%20done%3F%26nbsp%3B%3CBR%20%2F%3EIn%20attached%20sheet%2C%20I%20have%20this%3C%2FP%3E%3CP%3ESUMPRODUCT%3D((('Autumn%202022'!%24L%243%3A%24L%2410%3DA5)*('Autumn%202022'!%24O%243%3A%24O%2410))%2B%3C%2FP%3E%3CP%3E(('Autumn%202022'!%24Q%243%3A%24Q%2410%3DA5)*('Autumn%202022'!%24T%243%3A%24T%2410))%3CSTRONG%3E%2BETC%2B%3C%2FSTRONG%3E(('Autumn%202022'!%24L%2420%3A%24L%2431%3DA5)*('Autumn%202022'!%24O%2420%3A%24O%2431))%2B(('Autumn%202022'!%24Q%2420%3A%24Q%2431%3DA5)*('Autumn%202022'!%24T%2420%3A%24T%2431))%3CSTRONG%3E%2BETC%3C%2FSTRONG%3E)))%3C%2FP%3E%3CP%3EAny%20ideas%3F%3CBR%20%2F%3EI'd%20be%20nive%20to%20only%20have%20one%20column...%20Thanks%20in%20advance%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3005744%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-3005778%22%20slang%3D%22en-US%22%3ERe%3A%20SUMPRODUCT%20with%20multiple%20ranges%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3005778%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1227503%22%20target%3D%22_blank%22%3E%40RikkeB%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20cannot%20combine%20them%20in%20a%20single%20SUMPRODUCT%20since%20the%20ranges%20have%20different%20sizes.%20But%20you%20can%20use%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DSUMPRODUCT((('Autumn%202022'!%24L%2420%3A%24L%2431%3DA5)*('Autumn%202022'!%24O%2420%3A%24O%2431))%2B(('Autumn%202022'!%24Q%2420%3A%24Q%2431%3DA5)*('Autumn%202022'!%24T%2420%3A%24T%2431))%2B(('Autumn%202022'!%24V%2420%3A%24V%2431%3DA5)*('Autumn%202022'!%24Y%2420%3A%24Y%2431))%2B(('Autumn%202022'!%24AA%2420%3A%24AA%2431%3DA5)*('Autumn%202022'!%24AD%2420%3A%24AD%2431)))%2BSUMPRODUCT((('Autumn%202022'!%24L%243%3A%24L%2410%3DA5)*('Autumn%202022'!%24O%243%3A%24O%2410))%2B(('Autumn%202022'!%24Q%243%3A%24Q%2410%3DA5)*('Autumn%202022'!%24T%243%3A%24T%2410))%2B(('Autumn%202022'!%24V%243%3A%24V%2410%3DA5)*('Autumn%202022'!%24Y%243%3A%24Y%2410))%2B(('Autumn%202022'!%24AA%243%3A%24AA%2410%3DA5)*('Autumn%202022'!%24AD%243%3A%24AD%2410)))%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I am trying to "merge" two SUMPRODUCT calculations in one formula but get an N/A error. Both SUMPRODUCT formulas work on their own but not when I add them together. What am I doing wrong? Or can't it be done? 
In attached sheet, I have this

SUMPRODUCT=((('Autumn 2022'!$L$3:$L$10=A5)*('Autumn 2022'!$O$3:$O$10))+

(('Autumn 2022'!$Q$3:$Q$10=A5)*('Autumn 2022'!$T$3:$T$10))+ETC+(('Autumn 2022'!$L$20:$L$31=A5)*('Autumn 2022'!$O$20:$O$31))+(('Autumn 2022'!$Q$20:$Q$31=A5)*('Autumn 2022'!$T$20:$T$31))+ETC)))

Any ideas?
I'd be nive to only have one column... Thanks in advance

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

@RikkeB 

You cannot combine them in a single SUMPRODUCT since the ranges have different sizes. But you can use

 

=SUMPRODUCT((('Autumn 2022'!$L$20:$L$31=A5)*('Autumn 2022'!$O$20:$O$31))+(('Autumn 2022'!$Q$20:$Q$31=A5)*('Autumn 2022'!$T$20:$T$31))+(('Autumn 2022'!$V$20:$V$31=A5)*('Autumn 2022'!$Y$20:$Y$31))+(('Autumn 2022'!$AA$20:$AA$31=A5)*('Autumn 2022'!$AD$20:$AD$31)))+SUMPRODUCT((('Autumn 2022'!$L$3:$L$10=A5)*('Autumn 2022'!$O$3:$O$10))+(('Autumn 2022'!$Q$3:$Q$10=A5)*('Autumn 2022'!$T$3:$T$10))+(('Autumn 2022'!$V$3:$V$10=A5)*('Autumn 2022'!$Y$3:$Y$10))+(('Autumn 2022'!$AA$3:$AA$10=A5)*('Autumn 2022'!$AD$3:$AD$10)))

Thanks a lot Hans -again !! I hadn't even thought about that. This forum - and you- are a lifesaver