Sum Contents of a Column (2 cols on different sheets), Dependent on date

Babylon5

New Member
Joined
Sep 5, 2018
Messages
6
Good Morning,

I have am looking for help in how to SUM the contents of a column, if it falls within a particular week. I have established the formula to count the number of items, but just cant get it to sum !

This is my tab where I require the help,

Excel Help.xlsx
AFGH
34Wk. BeginningNo. of Auths '£' Amount of Auths
3506-Jan-201
3613-Jan-200
4623-Mar-201
5525-May-200
5601-Jun-203
5708-Jun-200
6427-Jul-203
6503-Aug-201
6610-Aug-200
6717-Aug-200
Week graph
Cell Formulas
RangeFormula
G35:G36,G46,G55:G57,G64:G67G35=(COUNTIFS(Live!T:T,">="&'Week graph'!A35,Live!T:T,"<"&'Week graph'!A35+7)+(COUNTIFS(Completed!T:T,">="&'Week graph'!A35,Completed!T:T,"<"&'Week graph'!A35+7)))


From there you can see I count the number of orders in column G, if they fall in the Week range in column A, ie. Date in Column A plus 7 days.

What I am looking for is the formula in column H that adds the jobs together if they fall within that active week. I think I am close, but could not get SUMIFS to work ?

The working sheets are below, titled Live (for active jobs) and Completed, for finished work. (Both sheets are exactly the same, I have only shown the live sheet below to save space).

Excel Help.xlsx
ST
1'£' of WorkDATE Work Authorised
2£1,513.8008/01/2020
3
4
5
6£500.0028/03/2020
7
8£999.0001/06/2020
9
10£1,650.0001/08/2020
11£900.0001/08/2020
12
13£5,000.0004/08/2020
14
15
16
17
18
Live



Can you help with the formula's in colum H please ?

Also, I have a Summary sheet, which shows the active week only, it would be good to display the totals for just the active week ? (currently I do this manually) >

Excel Help.xlsx
ABCDEFGH
24
25No. of Auths overall9'£' Amount of Auths Outside of Process since August 2020£12,562.80
26
27
28No. of Auths (This Week)'£' Amount of Auths Outside of Process (This Week)
29
30
31
32
33
Overview
Cell Formulas
RangeFormula
D25D25=COUNT(Live!$S$2:$S$9883)+COUNT(Completed!$S$2:$S$9883)
G25G25=SUM(Live!$S$2:$S$9883)+SUM(Completed!$S$2:$S$9883)


Any ideas ?

Many thanks,

Rich
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,574
Office Version
  1. 365
Platform
  1. Windows
How about
=SUMIFS(Live!S:S,Live!T:T,">="&A35,Live!T:T,"<"&A35+7)+SUMIFS(Completed!S:S,Completed!T:T,">="&A35,Completed!T:T,"<"&A35+7)))
 

Babylon5

New Member
Joined
Sep 5, 2018
Messages
6
Thank you Fluff, thats a great help, I was so close, but just couldn't get it to work.

I can easily de-construct the formula now to see how it works, so very much appreciated.

Do you have any idea how on my front Overview sheet I could keep a running total of what is done in the active week i.e. this week. On the formula above the equation works because there is a week to start from and plus 7 days, but is there a way to calculate the same based on what day it is today,?

Either way, thanks for your help !

Cheers Fluff.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,574
Office Version
  1. 365
Platform
  1. Windows
Just replace the A35 reference with Today()
 

Babylon5

New Member
Joined
Sep 5, 2018
Messages
6

ADVERTISEMENT

Hi Fluff, unfortunately that wouldn't work as that would be a rolling 7 day, each week begins on the Monday and last 7 days ?

Cheers
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,574
Office Version
  1. 365
Platform
  1. Windows
In that case use
TODAY()-WEEKDAY(TODAY(),3)
 

Babylon5

New Member
Joined
Sep 5, 2018
Messages
6
Spot on, Cheers Fluff ... Can see why you are a Moderator of this site ! :)

Very much appreciated, enjoy the rest of the day Sir !
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,574
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,656
Messages
5,626,130
Members
416,162
Latest member
Ashwin Alburg

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