SUMIF with VLOOKUP based on date range in multiple sheets

Juno49

New Member
Joined
Dec 3, 2020
Messages
5
Office Version
  1. 2011
Platform
  1. MacOS
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.
 

Some videos you may like

Excel Facts

Excel Can Read to You
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
Joined
Feb 6, 2012
Messages
1,708
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))

1607051534165.png


 

Juno49

New Member
Joined
Dec 3, 2020
Messages
5
Office Version
  1. 2011
Platform
  1. MacOS
Isn't that just for one sheet though? My lookup data is in a different sheet.
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,708
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
Joined
Dec 3, 2020
Messages
5
Office Version
  1. 2011
Platform
  1. MacOS
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
Joined
Dec 3, 2020
Messages
5
Office Version
  1. 2011
Platform
  1. MacOS
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!
 

Watch MrExcel Video

Forum statistics

Threads
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.
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
Top