Sumproduct error formula

[jazzista]

Hello: I am having errors when trying to use sumproduct with 3 conditions: range of dates, categories & amounts. What I am doing wrong with the formulas? . Can somebody take a look at my file? I was able to use sumifs however, I wanted to know/learn why I am getting #values error when using the sumproduct function . See file attached

Thanks in advance for the help

https://www.dropbox.com/s/vubagihiw7in62a/sumprodct question.xlsx?dl=0

 

[MARZIOTULLIO]

O4=SUMPRODUCT(($D$4:$D$23=$M4)*($C$4:$C$23>=$I$3)*($C$4:$C$23<=$J$3)*($E$4:$E$23)) copy down

O8=SUM(O4:O7)

 

[jazzista]

Marziotullio: Thank you very much for the elegant solution. What was I doing wrong? I am trying to understand the order of how sumproduct works. Thanks in advance!!

 

[MARZIOTULLIO]

[h=1]Excel Formula Efficiency 1: SUMPRODUCT function 12 Examples[/h]https://www.youtube.com/watch?v=O2ulQwMmlmY

[h=1]Slaying Excel Dragons Book #26 : SUMPRODUCT Function (12 Examples)[/h]https://www.youtube.com/watch?v=i4lEPYd1o18

look this tutorial

https://www.youtube.com/user/ExcelIsFun Mike Girvin tutorial everything on Excel

 

[joeu2004]

What was I doing wrong?

You wrote:
--($C$4:$C$23&">="&$I$3) instead of --($C$4:$C$23>=$I$3).
(--$C$4:$C$23&"<="&J3) instead of --($C$4:$C$23<=J3)
(--$D$4:$D$23=M4) instead of --($D$4:$D$23=M4)

The minimum correction for your formula is:

=SUMPRODUCT(--($C$4:$C$23>=$I$3),--($C$4:$C$23<=J3),--($D$4:$D$23=M4),$E$4:$E$23)

PS.... That addresses the #VALUE error. Once that is corrected, we find another usage error: J3 instead of $J$3.

So, the minimum corrections are:

=SUMPRODUCT(--($C$4:$C$23>=$I$3),--($C$4:$C$23<=$J$3),--($D$4:$D$23=M4),$E$4:$E$23)

FYI, SUMIFS is more efficient, when you can use it.

 

[jazzista]

joeu2004: Thanks for the explanation. Yes... I agree: sumifs is more efficient however I wanted to try sumproduct. One other thing, I did not know that when using sumproduct you do not need the &" "& construction where as sumifs you do need it. Thanks again for solving/explaining my errors.

 

[jazzista]

Marziotullio​

: thanks for the info and the links. Regards