SOLVED

Combine Sumproduct sumif or xlookup

Copper Contributor

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,

 

 

 

2 Replies
best response confirmed by Wim_Mortelmans (Copper Contributor)
Solution

Hi @Wim_Mortelmans 

 

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

DTE_0-1647266248113.png

=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 , 

 

Hi,

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

Many thanks !
Wim
1 best response

Accepted Solutions
best response confirmed by Wim_Mortelmans (Copper Contributor)
Solution

Hi @Wim_Mortelmans 

 

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

DTE_0-1647266248113.png

=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 , 

 

View solution in original post