SUMPRODUCT with multiple ranges?

[RikkeB]

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 · Accepted 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)))

[#3]

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