Log In
Register
Home
November 9, 2020
Offline
Hi again,
I have a table of numbers and I need to be able to sum the values in a particular column, from a particular start row and end row, based on selections.
For the attached example, I want to be able to sum the number in the column 'Product C' and the Size between 4 and 40.
Can anyone advise please?
Many thanks
Trusted Members
February 13, 2021
Offline
Hi Robert!
Use Index/Match to find the range you want inside a Sum.
=SUM(INDEX(Table1[[Product A]:[Product E]],MATCH(I3,Table1[Size],0),MATCH("Product "&I2,Table1[[#Headers],[Product A]:[Product E]],0)):INDEX(Table1[[Product A]:[Product E]],MATCH(I4,Table1[Size],0),MATCH("Product "&I2,Table1[[#Headers],[Product A]:[Product E]],0)))
🙂 Happy Excelling!
Trusted Members
Moderators
Power BI
January 31, 2022
Offline
As a variant you could use:
=SUM(MMULT((Table1[Size]>=I3)*(Table1[Size]<=I4),--(Table1[[#Headers],[Product A]:[Product E]]=H2&" "&I2))*Table1[[Product A]:[Product E]])
VIP
Trusted Members
June 25, 2016
Offline
Another solution
=SUMIFS(D:D,A:A,">="&I3,A:A,"<="&I4)
November 9, 2020
Offline
Thank you, Jessica, Riny and Sunny.
I am going to have to work through these to better understand how they operate. I was starting with INDIRECT but struggling to locate the correct ROW.
I also need to check that the ROW numbers in I3 and I4 exist. If they don't then they default to the Size number smaller.
So if I3 has 12 and I4 has 99, then the SUM is from Size 10 to Size 50.
Sunny - your solution only works on Column D (For Product C).
Many thanks
VIP
Trusted Members
December 7, 2016
Offline
Hello,
See attached file, should work fine as long as I3 is 54 or less. You can tweak it further if needed.
Br,
Anders
November 9, 2020
Offline
Thank you, Anders.
I can also see that if I remove the = sign from the inequality in Riny's solution, that I get the same result.
Thank you everyone. Much food for thought here. Once again, an excellent forum.
1 Guest(s)
