SOLVED

New Contributor

# SUMPRODUCT with multiple ranges?

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

# Re: SUMPRODUCT with multiple ranges?

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

# Re: SUMPRODUCT with multiple ranges?

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