Search code examples
google-sheetssumifsgoogle-sheets-formulagoogle-sheets-query

Is there a way to combine formula?

I'm making a bonus model for work. There are a lot of article numbers that the sheet has to check.

Right now I'm using this:

=SUMIFS(Data!$K$2:$K;Data!$I$2:$I;AA$2;Data!$G$2:$G;$A14)+

 SUMIFS(Data!$K$2:$K;Data!$I$2:$I;AB$2;Data!$G$2:$G;$A14)+

 SUMIFS(Data!$K$2:$K;Data!$I$2:$I;AC$2;Data!$G$2:$G;$A14)+

 SUMIFS(Data!$K$2:$K;Data!$I$2:$I;AD$2;Data!$G$2:$G;$A14)
 etc
 etc

Is there a way to combine everything in one Formula? AA2:AD2 doesn't work...

I hope I'm explaining it right :)

https://docs.google.com/spreadsheets/d/e/2PACX-1vRO-4tOpgZD97cvDEAjoCz-xK-7YhrjpzRlUBplpXOrwgssE2VYjuzdX3oBZdbNVk924J6La-Vy-52_/pubhtml

Solution

  • I'm confused with how your data works a bit. But am I right that you want to check for each salesperson if they sold any of the article numbers, and if that's the case sum the column C?

    Like so?

    enter image description here

    Formula I used translates to:

    {=SUMPRODUCT(($A$2:$A$107=F2)*($B$2:$B$107=TRANSPOSE($E$2:$E$15))*($C$2:$C$107))}

    And needs to be confirmerd through CtrlShiftEnter