Sum Data Across Columns and Rows between Dates

C

ChetanPuri

Board Regular
Joined
Sep 5, 2018
Messages
97
Office Version
  1. 365
Platform
  1. Windows
Hello Excel Team,
Just need help with a Sum formula to calculate the data on to Budget tab from July 2024-2025 (Source Sheet). The issue I get is that I have dates from Row I8 to Column BH 8. The Starting Date is 07 July 2023 and ends on 30 June 2024 and similarly I have data from I 9 to BH 135. On to my Budget Sheet I need to add the Data by Month End for Example Total Sales Revenue in July, August, Sep etc. I have included both the sheets below the 1st Sheet (Budget) that's where I want the Revenue by Month and second sheet is the source sheet which have the data by week.

Any help is appreciated.


Book5
ABCDEFGHIJKLMNO
2Monthly Trial Balance - Budget
3© www.excel-skills.com.au------------
4ClassAcc NoAccount DescriptionJul-2023Aug-2023Sep-2023Oct-2023Nov-2023Dec-2023Jan-2024Feb-2024Mar-2024Apr-2024May-2024Jun-2024
5I-01GIS-0105Revenue- 216,600.00- 433,200.00- 649,800.00- 866,400.00- 1,083,000.00- 1,299,600.00- 1,516,200.00- 1,732,800.00- 1,949,400.00- 2,166,000.00- 2,382,600.00- 2,599,200.00
6
7
8
Budget
Cell Formulas
RangeFormula
D4D4=DATE(YEAR('[CP Budget Workbook3.xlsx]Setup'!$C$10)-1,MONTH('[CP Budget Workbook3.xlsx]Setup'!$C$10)+2,0)
E4:O4E4=DATE(YEAR(D4),MONTH(D4)+2,0)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A4Expression=COUNTIF(BudClass,"No key!")>0textYES
D3:O3Expression=ROUND(D3,2)<>0textYES



Book5
IJKLMNOPQRSTUVWXYZAAAB
807-Jul-2314-Jul-2321-Jul-2328-Jul-2304-Aug-2311-Aug-2318-Aug-2325-Aug-2301-Sep-2308-Sep-2315-Sep-2322-Sep-2329-Sep-2306-Oct-2313-Oct-2320-Oct-2327-Oct-2303-Nov-2310-Nov-2317-Nov-23
93,015.983,015.983,015.983,015.983,015.983,015.983,015.983,015.983,015.983,015.983,015.983,015.983,015.983,015.983,015.983,015.983,015.983,015.983,015.983,015.98
10
112,634.002,634.002,634.002,634.002,634.002,634.002,634.002,634.002,634.002,634.002,634.002,634.002,634.002,634.002,634.002,634.002,634.002,634.002,634.002,634.00
125,007.995,007.995,007.995,007.995,007.995,007.995,007.995,007.995,007.995,007.995,007.995,007.992,861.71
132,146.285,007.995,007.995,007.995,007.995,007.995,007.995,007.99
143,931.992,808.56
151,123.433,931.993,931.993,931.993,931.993,931.993,931.993,931.993,931.993,931.993,931.993,931.993,931.993,931.993,931.993,931.993,931.993,931.993,931.99
1613,236.4713,236.4713,236.4713,236.4713,236.4713,236.4713,236.4713,236.4713,236.4713,236.477,563.70
175,387.2512,570.2512,570.2512,570.2512,570.2512,570.2512,570.2512,570.2512,570.2512,570.25
186,203.596,203.596,203.596,203.596,203.596,203.596,203.596,203.596,203.596,203.593,544.91
192,658.686,203.596,203.596,203.596,203.596,203.596,203.596,203.596,203.596,203.59
205,738.855,738.855,738.855,738.855,738.855,738.855,738.855,738.855,738.855,738.855,738.855,738.855,738.855,738.855,738.855,738.855,738.855,738.855,738.855,738.85
217,101.607,101.607,101.607,101.607,101.607,101.607,101.607,101.607,101.607,101.607,101.605,072.57
222,029.037,101.607,101.607,101.607,101.607,101.607,101.607,101.607,101.60
237,963.527,963.527,963.527,963.527,963.527,963.527,963.527,963.527,963.527,963.527,963.527,963.527,963.527,963.523,412.94
244,531.757,930.567,930.567,930.567,930.567,930.56
256,181.186,181.186,181.186,181.186,181.186,181.186,181.186,181.186,181.186,181.186,181.186,181.184,415.13
Revenue July 2024-2025
Cells with Conditional Formatting
CellConditionCell FormatStop If True
I8:BH8Expression=$I$8>#REF!textNO
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Sort by date Sort by votes
You're also referring to a workbook "CP Budget Workbook3" in a sheet called "Setup"?
is that required for this question?

And what cells are referred to with the name: "BudClass"?

Is every row of your Revenue July 2024-2025 sheet for the "Class" and "Acct No" on the budget sheet?
 
Last edited:
Upvote 0
awoohaw said:
You're also referring to a workbook "CP Budget Workbook3" in a sheet called "Setup"?
is that required for this question?

And what cells are referred to with the name: "BudClass"?
Click to expand...
Thank you for your response, you can ignore the "BudClass", I just need the Total in Row 5 from Column D to O on the Budget worksheet.

"Row 4 Column D to O are the Dates/Months from "Setup" on the Budget Worksheet and its not required for this question.

Many thanks,

Regards,
Chetan
 
Upvote 0
Okay, try this:
I've Recalculated the Dates on Budget Sheet.
Book3
ABCDEFGHIJKLMNO
1
2Monthly Trial Balance - Budget
3© www.excel-skills.com.au000000000000
4ClassAcc NoAccount DescriptionJul-2023Aug-2023Sep-2023Oct-2023Nov-2023Dec-2023Jan-2024Feb-2024Mar-2024Apr-2024May-2024Jun-2024
5I-01GIS-0105Revenue-216600-433200-649800-866400-1083000-1299600-1516200-1732800-1949400-2166000-2382600-2599200
6formula:244,060.68244,060.68301,691.82216,586.31162,404.420.000.000.000.000.000.000.00
7
Budget
Cell Formulas
RangeFormula
E4:O4E4=EDATE(D4,1)
D6:O6D6=LET( Data,'Revenue July 2024-2025'!$A$2:$T$18, Dates,'Revenue July 2024-2025'!$A$1:$T$1, TargetFOM,EOMONTH(D4,-1)+1, TargetEOM,EOMONTH(D4,0), SUMPRODUCT((TargetFOM<=Dates)*(TargetEOM>=Dates)*(Data)))
 
Upvote 0
awoohaw said:
Okay, try this:
I've Recalculated the Dates on Budget Sheet.
Book3
ABCDEFGHIJKLMNO
1
2Monthly Trial Balance - Budget
3© www.excel-skills.com.au000000000000
4ClassAcc NoAccount DescriptionJul-2023Aug-2023Sep-2023Oct-2023Nov-2023Dec-2023Jan-2024Feb-2024Mar-2024Apr-2024May-2024Jun-2024
5I-01GIS-0105Revenue-216600-433200-649800-866400-1083000-1299600-1516200-1732800-1949400-2166000-2382600-2599200
6formula:244,060.68244,060.68301,691.82216,586.31162,404.420.000.000.000.000.000.000.00
7
Budget
Cell Formulas
RangeFormula
E4:O4E4=EDATE(D4,1)
D6:O6D6=LET( Data,'Revenue July 2024-2025'!$A$2:$T$18, Dates,'Revenue July 2024-2025'!$A$1:$T$1, TargetFOM,EOMONTH(D4,-1)+1, TargetEOM,EOMONTH(D4,0), SUMPRODUCT((TargetFOM<=Dates)*(TargetEOM>=Dates)*(Data)))
Click to expand...
Thank you, but when I enter the formula I get the following:
Book6
ABCD
3© www.excel-skills.com.au-
4ClassAcc NoAccount DescriptionJul-2023
5I-01GIS-0105Sales - Fruit Juice
6#REF!
7
8
9
10
11
12
13
14
15
Budget
Cell Formulas
RangeFormula
D6D6=LET( Data,'Revenue July 2024-2025'!$A$2:$T$18, Dates,'Revenue July 2024-2025'!$A$1:$T$1, TargetFOM,EOMONTH(D4,-1)+1, TargetEOM,EOMONTH(D4,0), SUMPRODUCT((TargetFOM<=Dates)*(TargetEOM>=Dates)*(Data)))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A4Expression=COUNTIF(BudClass,"No key!")>0textYES
D3:O3Expression=ROUND(D3,2)<>0textYES
 
Upvote 0
ChetanPuri said:
Thank you, but when I enter the formula I get the following:
Book6
ABCD
3© www.excel-skills.com.au-
4ClassAcc NoAccount DescriptionJul-2023
5I-01GIS-0105Sales - Fruit Juice
6#REF!
7
8
9
10
11
12
13
14
15
Budget
Cell Formulas
RangeFormula
D6D6=LET( Data,'Revenue July 2024-2025'!$A$2:$T$18, Dates,'Revenue July 2024-2025'!$A$1:$T$1, TargetFOM,EOMONTH(D4,-1)+1, TargetEOM,EOMONTH(D4,0), SUMPRODUCT((TargetFOM<=Dates)*(TargetEOM>=Dates)*(Data)))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A4Expression=COUNTIF(BudClass,"No key!")>0textYES
D3:O3Expression=ROUND(D3,2)<>0textYES
Click to expand...
Sorry just a silly question on my Revenue Worksheet my target Data is I 9 to AB 25 , but in the formula (Let Data, Revenue is Targeting A2:T18?
 
Upvote 0
Yes. that is probably the issue. I copied your data to cell A1 instead of I8.

try this:

=LET( Data,'Revenue July 2024-2025'!$I$8:$AB$26,
Dates,'Revenue July 2024-2025'!$I$1:$AB$1,
TargetFOM,EOMONTH(D4,-1)+1,
TargetEOM,EOMONTH(D4,0),
SUMPRODUCT((TargetFOM<=Dates)*(TargetEOM>=Dates)*(Data)))
 
Upvote 0
awoohaw said:
Yes. that is probably the issue. I copied your data to cell A1 instead of I8.

try this:

=LET( Data,'Revenue July 2024-2025'!$I$8:$AB$26,
Dates,'Revenue July 2024-2025'!$I$1:$AB$1,
TargetFOM,EOMONTH(D4,-1)+1,
TargetEOM,EOMONTH(D4,0),
SUMPRODUCT((TargetFOM<=Dates)*(TargetEOM>=Dates)*(Data)))
Click to expand...
Great thank you, will try now
awoohaw said:
Yes. that is probably the issue. I copied your data to cell A1 instead of I8.

try this:

=LET( Data,'Revenue July 2024-2025'!$I$8:$AB$26,
Dates,'Revenue July 2024-2025'!$I$1:$AB$1,
TargetFOM,EOMONTH(D4,-1)+1,
TargetEOM,EOMONTH(D4,0),
SUMPRODUCT((TargetFOM<=Dates)*(TargetEOM>=Dates)*(Data)))
Click to expand...
Sorry Still getting the Same error, also, I just wanted add another criteria (if possible), I need to calculate Revenue by Location, so the Location Account No.s are in column B5 to B8 and are in column B8 to 30 in Source Sheet.
MR Excel Help.xlsx
ABCDEFGHIJKLMNOP
4ClassAcc NoAccount DescriptionJul-2024Aug-2024Sep-2024Oct-2024Nov-2024Dec-2024Jan-2025Feb-2025Mar-2025Apr-2025May-2025Jun-2025Total
5I-01G20.23.120.401SIL-Revenue-ASP2,174,892.882,730,749.372,182,254.872,179,703.582,724,002.582,176,410.162,718,840.772,182,264.932,176,696.292,174,605.462,718,960.142,181,211.6528,320,592.68
6I-01G20.23.100.401SIL-Revenue-DRW#VALUE!#VALUE!
7I-01G20.23.110.401SIL-Revenue-KTH#VALUE!
8I-01G20.23.140.401SIL-Revenue-PLM
9
Budget
Cell Formulas
RangeFormula
E4:O4F4=DATE(YEAR(E4),MONTH(E4)+2,0)
D5:O5F5=SUMIFS('Revenue July 2024-2025'!$D$221:$BC$221,'Revenue July 2024-2025'!$D$8:$BC$8,">="&DATE(YEAR(Budget!F4),MONTH(Budget!F4),1),'Revenue July 2024-2025'!$D$8:$BC$8,"<="&EOMONTH(Budget!F4,0))
P5P5=SUM(D5:O5)
D6:E6E6=LET(Data,'Revenue July 2024-2025'!$D$9:$BC$220,Dates,'Revenue July 2024-2025'!$D$8:$BC$8,TargetFOM,EOMONTH(Budget!E4,-1)+1,TargetEOM,EOMONTH(Budget!E4,0),SUMPRODUCT((TargetFOM<=Dates)*(TargetEOM>=Dates)*(Data)))
D4D4=DATE(YEAR('[CP Budget Workbook3.xlsx]Setup'!$C$10)-1,MONTH('[CP Budget Workbook3.xlsx]Setup'!$C$10)+2,0)
D7D7=LET( Data,'Revenue July 2024-2025'!$D$8:$N$26,Dates,'Revenue July 2024-2025'!$D$1:$N$1,TargetFOM,EOMONTH(D4,-1)+1,TargetEOM,EOMONTH(D4,0),SUMPRODUCT((TargetFOM<=Dates)*(TargetEOM>=Dates)*(Data)))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A4Expression=COUNTIF(BudClass,"No key!")>0textYES


MR Excel Help.xlsx
BCDEFGHIJKL
8Account No.Region05-Jul-2412-Jul-2419-Jul-2426-Jul-2402-Aug-2409-Aug-2416-Aug-2423-Aug-2430-Aug-24
920.23.140.401Palmerston3,386.903,386.903,386.903,386.903,386.903,386.903,386.903,386.903,386.90
1020.23.140.401Palmerston
11
1220.23.100.401Darwin2,781.062,781.062,781.062,781.062,781.062,781.062,781.062,781.062,781.06
1320.23.100.401Darwin
14
1520.23.110.401Katherine5,273.415,273.415,273.415,273.415,273.415,273.415,273.415,273.415,273.41
1620.23.110.401Katherine
17
1820.23.100.401Darwin4,151.534,151.53
1920.23.100.401Darwin593.084,151.534,151.534,151.534,151.534,151.534,151.534,151.53
20
2120.23.100.401Darwin13,272.1113,272.1113,272.1113,272.1113,272.1113,272.1113,272.1113,272.1113,272.11
2220.23.100.401Darwin
23
2420.23.140.401Palmerston6,532.386,532.386,532.386,532.386,532.386,532.386,532.386,532.386,532.38
2520.23.140.401Palmerston
26
2720.23.140.401Palmerston6,059.286,059.286,059.286,059.286,059.286,059.286,059.286,059.286,059.28
2820.23.140.401Palmerston
29
3020.23.140.401Palmerston7,477.987,477.987,477.987,477.987,477.987,477.987,477.987,477.987,477.98
3120.23.140.401Palmerston
32
3320.23.100.401Darwin8,350.888,350.888,350.888,350.888,350.888,350.888,350.888,350.888,350.88
3420.23.100.401Darwin
35
3620.23.100.401Darwin4,853.334,853.334,853.334,853.334,853.334,853.334,853.334,853.334,853.33
3720.23.100.401Darwin
38
3920.23.140.401Palmerston3,894.673,894.673,894.673,894.673,894.67556.38
4020.23.140.401Palmerston3,894.673,894.673,894.673,894.67
41
4220.23.140.401Palmerston6,043.836,043.836,043.836,043.836,043.836,043.836,043.836,043.836,043.83
4320.23.140.401Palmerston
Revenue July 2024-2025
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D8:BC8Expression=$D$8>#REF!textNO
.

Thank you for all your help. Much appreciated.
 
Upvote 0
ChetanPuri said:
Thank you, but when I enter the formula I get the following:
Book6
ABCD
3© www.excel-skills.com.au-
4ClassAcc NoAccount DescriptionJul-2023
5I-01GIS-0105Sales - Fruit Juice
6#REF!
7
8
9
10
11
12
13
14
15
Budget
Cell Formulas
RangeFormula
D6D6=LET( Data,'Revenue July 2024-2025'!$A$2:$T$18, Dates,'Revenue July 2024-2025'!$A$1:$T$1, TargetFOM,EOMONTH(D4,-1)+1, TargetEOM,EOMONTH(D4,0), SUMPRODUCT((TargetFOM<=Dates)*(TargetEOM>=Dates)*(Data)))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A4Expression=COUNTIF(BudClass,"No key!")>0textYES
D3:O3Expression=ROUND(D3,2)<>0textYES
Click to expand...
why are you putting the formula on the row below?
 
Upvote 0
Okay, try this:

I suggest posting these into a new workbook first to get the proof of concept established. If it works then, then you have what you need to do.


Book1
ABCDEFGHIJKLMNOP
1
2
3
4ClassAcc NoAccount Description2024-07-012024-08-012024-09-012024-10-012024-11-012024-12-012025-01-012025-02-012025-03-012025-04-012025-05-012025-06-01Total
5I-01G20.23.120.401SIL-Revenue-ASP0.000.000.000.000.000.000.000.000.000.000.000.000.00
6I-01G20.23.100.401SIL-Revenue-DRW134,228.76167,044.600.000.000.000.000.000.000.000.000.000.00301,273.36
7I-01G20.23.110.401SIL-Revenue-KTH21,093.6526,367.060.000.000.000.000.000.000.000.000.000.0047,460.72
8I-01G20.23.140.401SIL-Revenue-PLM133,580.18167,531.600.000.000.000.000.000.000.000.000.000.00301,111.78
Budget
Cell Formulas
RangeFormula
E4:O4E4=EDATE(D4,1)
D5:O8D5=LET( Data,'Revenue July 2024-2025'!$D$9:$L$43, AccNos,'Revenue July 2024-2025'!$B$9:$B$43, Dates,'Revenue July 2024-2025'!$D$8:$L$8, AccNo,$B5, TargetFOM,EOMONTH(D$4,-1)+1, TargetEOM,EOMONTH(D$4,0), SUMPRODUCT((TargetFOM<=Dates)*(TargetEOM>=Dates)*(Data)*(AccNo=AccNos)))
P5:P8P5=SUM(D5:O5)





Book1
ABCDEFGHIJKLM
1
2
3
4
5
6
7
8Account No.Region2024-07-052024-07-122024-07-192024-07-262024-08-022024-08-092024-08-162024-08-232024-08-30
920.23.140.401Palmerston3,386.903,386.903,386.903,386.903,386.903,386.903,386.903,386.903,386.90
1020.23.140.401Palmerston
11
1220.23.100.401Darwin2,781.062,781.062,781.062,781.062,781.062,781.062,781.062,781.062,781.06
1320.23.100.401Darwin
14
1520.23.110.401Katherine5,273.415,273.415,273.415,273.415,273.415,273.415,273.415,273.415,273.41
1620.23.110.401Katherine
17
1820.23.100.401Darwin4,151.534,151.53
1920.23.100.401Darwin593.084,151.534,151.534,151.534,151.534,151.534,151.534,151.53
20
2120.23.100.401Darwin13,272.1113,272.1113,272.1113,272.1113,272.1113,272.1113,272.1113,272.1113,272.11
2220.23.100.401Darwin
23
2420.23.140.401Palmerston6,532.386,532.386,532.386,532.386,532.386,532.386,532.386,532.386,532.38
2520.23.140.401Palmerston
26
2720.23.140.401Palmerston6,059.286,059.286,059.286,059.286,059.286,059.286,059.286,059.286,059.28
2820.23.140.401Palmerston
29
3020.23.140.401Palmerston7,477.987,477.987,477.987,477.987,477.987,477.987,477.987,477.987,477.98
3120.23.140.401Palmerston
32
3320.23.100.401Darwin8,350.888,350.888,350.888,350.888,350.888,350.888,350.888,350.888,350.88
3420.23.100.401Darwin
35
3620.23.100.401Darwin4,853.334,853.334,853.334,853.334,853.334,853.334,853.334,853.334,853.33
3720.23.100.401Darwin
38
3920.23.140.401Palmerston3,894.673,894.673,894.673,894.673,894.67556.38
4020.23.140.401Palmerston3,894.673,894.673,894.673,894.67
41
4220.23.140.401Palmerston6,043.836,043.836,043.836,043.836,043.836,043.836,043.836,043.836,043.83
4320.23.140.401Palmerston
Revenue July 2024-2025
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
AVERAGEIF TROUBLES
Replies
3
Views
79
AhoyNC
A
Lacan
  • Solved
Convert Months Spanish Language to Portuguese and do Lookups + Sum?
Replies
2
Views
190
Lacan
Lacan
J
  • Question Question
adding cells based on criteria across several columns
Replies
5
Views
999
Cubist
Cubist
S
  • Question Question
SUM and CHOOSE formula question
Replies
7
Views
260
Fluff
Fluff
S
  • Solved
lookup return from range of dates
Replies
4
Views
511
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,816
Messages
6,181,139
Members
453,021
Latest member
Justyna P

Share this page

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
Back
Top