Forum

Notifications
Clear all

Sum values in a table based on 3 variables - column, UpperRow and LowerRow

7 Posts
5 Users
0 Reactions
117 Views
(@robbieg)
Posts: 39
Trusted Member
Topic starter
 

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

 
Posted : 15/03/2022 3:01 pm
(@jstewart)
Posts: 216
Estimable Member
 

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!

 
Posted : 15/03/2022 7:50 pm
Riny van Eekelen
(@riny)
Posts: 1210
Member Moderator
 

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]])

 
Posted : 16/03/2022 2:19 am
(@sunnykow)
Posts: 1417
Noble Member
 

Another solution

=SUMIFS(D:D,A:A,">="&I3,A:A,"<="&I4)

 
Posted : 16/03/2022 5:27 am
(@robbieg)
Posts: 39
Trusted Member
Topic starter
 

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

 
Posted : 16/03/2022 6:28 am
Anders Sehlstedt
(@sehlsan)
Posts: 972
Prominent Member
 

Hello,

See attached file, should work fine as long as I3 is 54 or less. You can tweak it further if needed.

Br,
Anders

 
Posted : 16/03/2022 6:40 pm
(@robbieg)
Posts: 39
Trusted Member
Topic starter
 

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.

 
Posted : 17/03/2022 5:48 am
Share: