Zdesgain11

New Member
Joined
Feb 28, 2019
Messages
4
I currently have a set of data as follows:

Jan-18Feb-18Mar-18Apr-18Jun-18Jul-18
A123456
B789101112
C13
1415161718
D192021222324

<tbody>
</tbody>

I have a dynamic lookup value for the date and want to be able to sum the values that are less than or equal to the month I choose.

For example, If I am looking at A, and want to choose April, I would want to sum April, March, Feb and Jan for a total of 4+3+2+1 = 10.

Does anyone know how to make this possible? I thought about a Sumif Index Match Match formula, but im not sure the best way to set it up.
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Try this:

Excel Workbook
ABCDEFGH
1jan-18feb-18mar-18apr-18may-18jun-18Sum Apr
212345610
3789101112
4131415161718
5192021222324
Hoja3
 
Upvote 0

Book1
ABCDEFGHIJ
11/1/20182/1/20183/1/20184/1/20186/1/20187/1/2018A
2A1234564/1/2018
3B78910111210
4C131415161718
5D192021222324
Sheet1


In J3 enter:

=SUMIFS(INDEX($B$2:$G$5,MATCH(J1,$A$2:$A$5,0),0),$B$1:$G$1,"<="&J2)
 
Upvote 0
Thank you! That does work - I am trying to take it to the next level though, for example:

I have multiple "customers" on my other tab in separate columns as follows:

Customer 1Customer 2Customer 3Customer 4
A
B
C
D

<tbody>
</tbody>

my data set has data mentioned below for each customer as follows:
Jan 2018Feb 2018Mar 2018Apr 2018May 2018Jun 2018
Customer 1-A
Customer 1-B
Customer 1-C
Customer 1-D

<tbody>
</tbody>

Customer 2-A
Customer 2-B
Customer 2-C
Customer 2-D

<tbody>
</tbody>

Is there anyway to match the customer on the first tab to the product and then sum the values less than or equal to the date?
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top