CODEWITHSUNDEEP
excelvalidationdateexcel-formula
Adam Phillips
Adam Phillips

Reputation: 13

SUMIFS to sum data between custom date range

I am building a dashboard to summarize purchase data. I have a worksheet for purchase data, there is a reference tables worksheet with the start and end dates for fiscal months, with the associated fiscal year, and fiscal month (summarized below), and the dashboard worksheet.

Table: oetFiscalMonths

Start Date | End Date | Month    | Year  
10/1/17    | 10/30/17 | October  | 2018  
10/31/17   | 11/1/17  | November | 2018

On the dashboard there is a table of data formatted as below

         | Beginning Budget | Total Purchases | Remaining Budget |  
October  | $XXXXXXXXX.00    | $XXXXXX.00      | $XXXXXXXXXX.00   |  
November | $XXXXXXXXX.00    | $XXXXXX.00      | $XXXXXXXXXX.00   |  
December | $XXXXXXXXX.00    | $XXXXXX.00      | $XXXXXXXXXX.00   |  
January  | $XXXXXXXXX.00    | $XXXXXX.00      | $XXXXXXXXXX.00   |

The formula I am looking to enter would go in the Total purchases, and would sum all purchases made in the fiscal month on the right, based on the table for fiscal months, while meeting several other criteria, which I have been able to complete with the SUMIFS formula. I am just not sure how to handle the date range portion as they do not follow the calendar months even remotely mostly, and the table for the dates is formatted the way it is as several other portions utilize its current format.

Any help is greatly appreciated!

Upvotes: 1

Views: 698

Answers (3)

J.Mapz
J.Mapz

Reputation: 511

Assuming sheet1 is your dashboard, sheet2 contains your oetFiscalMonths table and sheet3 contains the purchases and their corresponding dates of purchase you want to SUM, you can make use of SUMIFS combined with INDEX + MATCH to suite your current file structure:

=SUMIFS(sheet3!C:C,sheet3!B:B,">="&INDEX(sheet2!A:A,MATCH(sheet1!A2,sheet2!C:C,0)),sheet3!B:B,"<="&INDEX(sheet2!B:B,MATCH(sheet1!A2,sheet2!C:C,0)))

Legends are as follows:

sheet3!C:C contains the purchase values you want to SUM

sheet3!B:B contains the date of purchases

sheet2!A:A contains the start dates of your months

sheet2!B:B contains the end dates of your months

sheet2!C:C contains your months

sheet1!A2 contains the first month you want to get the SUM of purchases of.

Upvotes: 0

Shoaib Hossain
Shoaib Hossain

Reputation: 17

You can try also by sumproduct formula, its benefit without open worksheet formula calculating.

=SUMPRODUCT(--(B:B>=H3)*(B:B<=I3),(C:C))

C:C = Sum range

B:B = Date search range

H3 = Value of start date

I3 = Value of end date

Upvotes: 1

Senor Penguin
Senor Penguin

Reputation: 405

You can use the SUMIFS like I've shown below. Excuse the random row and cell usage. I will try to explain below.

=SUMIFS(C:C,A:A,">="&G1,A:A,"<="&H1)

C:C = Sum range
A:A = Date search range
G1 = Value of start date
H1 = Value of end date

EDIT: I would suggest moving the months column to the front and then the dates after like shown below. That way you can use a vlookup to find the date and then incorporate a vlookup into the sumifs.

  Month  | Start Date | End Date | Year  
October  | 10/1/17    | 10/30/17 | 2018  
November | 10/31/17   | 11/1/17  | 2018

=SUMIFS(G:G,F:F,">="&VLOOKUP(J6,M:O,2,0),F:F,"<="&VLOOKUP(J6,M:O,3,0))

M:O is your fiscal year table
J6 is where you would type the month you are searching for
Column 2 in the first VLOOKUP will give you start date
Column 3 in the second VLOOKUP will give you end date


Let me know if you have any questions, it may be kind of confusing since I am using random rows and columns.

Upvotes: 0

Related Questions