# SUMIF with VLOOKUP based on date range in multiple sheets

#### Juno49

##### New Member
Hi,

I have a sheet called "Orders" in which I add each order, its date and amount.

In a separate sheet, I have a monthly summary table.

I want a cell in the summary table to look at the "Orders" sheet, and if the date (in row G) falls between Jan 1 and Jan 31, I want the total from column AE to show in the summary table. I'll do this for each month.

This is what I have so far:

=SUMIF(VLOOKUP(AE4,AE5,"Orders”G4:G5>=1-Jan-2021″,G4:G5,”<=31-Jan-2021″,1))

Obviously that isn't right (I'm just playing around and don't really know what I'm doing!), but maybe I'm getting close.

Note that new rows (i.e., orders) will be added to the Orders sheet throughout the year, so I want AE5 and G5 in the formula to update automatically as I add a new row.

Any help much appreciated.

### Excel Facts

Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

#### mrshl9898

##### Well-known Member
Not sure what you are looking at. Maybe something like this?

a2 = 01/01/2020 (formatted as Mmm-YY)
b2 = =SUMIFS(AE:AE,G:G,">="&A2,G:G,"<="&EOMONTH(A2,0))

 ​

#### Juno49

##### New Member
Isn't that just for one sheet though? My lookup data is in a different sheet.

#### mrshl9898

##### Well-known Member
You can add sheet names before the Column references AE:AE could be 'Sheet1'!AE:AE for example. AE and G need to just be on the same Sheet

#### Juno49

##### New Member
Okay, so now I have: =SUMIFS('Orders'!AE4:AE5;'Orders'!G4:G5;">=01/01/2021″,G4:G5,”<=01/31/2021″,0)

What I'm aiming for: If any data in the "Orders" sheet, cells AE4 to AE5, is a date between the beginning and end of January 2021, then add the figures in "Orders", cells G4 to G5, and return the answer to cell B2 the sheet called "Summary".

But it's still returning an error.
Thank you for your help so far!

#### Juno49

##### New Member
Okay, found it! The formula that has worked is

=SUMIFS(Orders!\$AE4:\$AE5,Orders!\$G4:\$G5,">=1/1/21",Orders!\$G4:\$G5,"<=1/31/21")

Replies
1
Views
78
Replies
8
Views
88
Replies
4
Views
168
Replies
14
Views
137
Replies
5
Views
54

1,127,123
Messages
5,622,870
Members
415,935
Latest member
kes1973

### 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.

### Which adblocker are you using?

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

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