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

#### Babylon5

##### New Member
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

### 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
=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
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
Just replace the A35 reference with Today()

#### Babylon5

##### New Member

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
In that case use
TODAY()-WEEKDAY(TODAY(),3)

#### Babylon5

##### New Member
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
You're welcome & thanks for the feedback.

Replies
0
Views
152
Replies
1
Views
232
Replies
0
Views
92
Replies
1
Views
139
Replies
0
Views
118

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.

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