SUMIF with VLOOKUP based on date range in multiple sheets

[Juno49]

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.

 

[mrshl9898]

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]

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

 

[mrshl9898]

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]

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]

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

Thanks for all your help!