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
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!
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]])
Another solution
=SUMIFS(D:D,A:A,">="&I3,A:A,"<="&I4)
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
Hello,
See attached file, should work fine as long as I3 is 54 or less. You can tweak it further if needed.
Br,
Anders
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.
