Forum Discussion

RikkeB's avatar
RikkeB
Copper Contributor
Nov 25, 2021
Solved

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...
excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    Nov 25, 2021

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

HansVogelaar's avatar
HansVogelaar
MVP
Nov 25, 2021

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

  • RikkeB's avatar
    RikkeB
    Copper Contributor
    Nov 25, 2021
    Thanks a lot Hans -again !! I hadn't even thought about that. This forum - and you- are a lifesaver

Resources