Mar 11 2022 01:27 AM
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,
Mar 14 2022 06:58 AM
Solution
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 ,
Mar 14 2022 07:31 AM
Mar 14 2022 06:58 AM
Solution
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 ,