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

[Babylon5]

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
	A					F	G	H
34	Wk. Beginning						No. of Auths	'£' Amount of Auths
35	06-Jan-20						1
36	13-Jan-20						0
46	23-Mar-20						1
55	25-May-20						0
56	01-Jun-20						3
57	08-Jun-20						0
64	27-Jul-20						3
65	03-Aug-20						1
66	10-Aug-20						0
67	17-Aug-20						0
Week graph

Cell Formulas
Range	Formula
G35:G36,G46,G55:G57,G64:G67	G35	=(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
	S	T
1	'£' of Work	DATE Work Authorised
2	£1,513.80	08/01/2020
3
4
5
6	£500.00	28/03/2020
7
8	£999.00	01/06/2020
9
10	£1,650.00	01/08/2020
11	£900.00	01/08/2020
12
13	£5,000.00	04/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
	A	B	C	D	E	F	G	H
24
25		No. of Auths overall		9		'£' Amount of Auths Outside of Process since August 2020	£12,562.80
26
27
28		No. of Auths (This Week)				'£' Amount of Auths Outside of Process (This Week)
29
30
31
32
33
Overview

Cell Formulas
Range	Formula
D25	D25	=COUNT(Live!$S$2:$S$9883)+COUNT(Completed!$S$2:$S$9883)
G25	G25	=SUM(Live!$S$2:$S$9883)+SUM(Completed!$S$2:$S$9883)

Any ideas ?

Many thanks,

Rich

 

[Fluff]

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]

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]

Just replace the A35 reference with Today()

 

[Babylon5]

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]

In that case use
TODAY()-WEEKDAY(TODAY(),3)

 

[Babylon5]

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]

You're welcome & thanks for the feedback.