Forum Discussion

Wim_Mortelmans's avatar
Wim_Mortelmans
Copper Contributor
Mar 11, 2022
Solved

Combine Sumproduct sumif or xlookup

Hi all,

 

I am struggling with the following....

 

I have column H (called "Nature"), Column J (called "Weight") Column M ("Jan-22")

Column "Nature" contains 3 possible values such as "Rally", "Baseline","Other"

Column Weight has % values such as 0%, 25%, 50%, 75% 100% 

Column "Jan-22" contains numbers (in currency format to be complete)

I have values in those columns  from row 14 till 48...

 

 

I want a total per "Nature" which is the sum of the product (Weight * Jan-22")

 

So in the end I will have

 

"Weighted Rally ="

"Weighted Baseline ="

"Weighted Other ="

 

That will be placed be below the last row in column Jan-22 (and later I will copy it for the other months of year of course.)

 

It looked simple at first but I can't get it to work...Been playing with SUMIF;SUMPRODUCT combining with XLOOKUP but as of yet not smart enough to make it work 🙂

 

Anybody can crack this formula?

 

Thanks,

 

 

 

excel
  • Martin_Weiss's avatar
    Martin_Weiss
    Mar 14, 2022

    Hi Wim_Mortelmans 

     

    if I understood everything correctly, the SUMPRODUCT function should be sufficient:

    =SUMPRODUCT($J$2:$J$10;$M$2:$M$10;--($H$2:$H$10=L14))

     

    Depending on your regional settings, you might need to replace the ; with , 

     

2 Replies

  • Martin_Weiss's avatar
    Martin_Weiss
    Bronze Contributor
    Mar 14, 2022

    Hi Wim_Mortelmans 

     

    if I understood everything correctly, the SUMPRODUCT function should be sufficient:

    =SUMPRODUCT($J$2:$J$10;$M$2:$M$10;--($H$2:$H$10=L14))

     

    Depending on your regional settings, you might need to replace the ; with , 

     

    • Wim_Mortelmans's avatar
      Wim_Mortelmans
      Copper Contributor
      Mar 14, 2022
      Hi,

      Thank you, that is indeed what I am looking for. 🙂

      Many thanks !
      Wim