Sumif on certain dates for data in same Column

[martydh3]

Hi,

My data source has Dates and Values for elements of work in the same column.

Dataset

View attachment 103530

I am trying to create on seperate excel file a budget that tells me value of work that is complete every month in below format.

View attachment 103531
Is there a formula to make this work?

Thanks in advance

Marty

 

[alansidman]

Marty,
MrE tells me that the attachments could not be found. Suggest you upload your samples using XL2BB

 

[martydh3]

Hi,

My data source has Dates and Values for elements of work in the same column.

Dataset

Type	Plot	Design Fees	Warranty Costs	Site Setup	Prelims (note +2.77% monthly)	Substructure	Foundations Concrete	Blockwork	Subfloors	Piling	Upper Floors	Roof	Roof Coverings	Roof Trusess To Apartments s+f
Value	1	4,208.42	1,052.11	526.05	8,942.90	-	2,104.21	2,209.42	2,209.42	841.68	1,052.11	-	2,735.48	420.84
Value	2	4,208.42	1,052.11	526.05	8,942.90	-	2,104.21	2,209.42	2,209.42	841.68	1,052.11	-	2,735.48	420.84
Value	3	4,208.42	1,052.11	526.05	8,942.90	-	2,104.21	2,209.42	2,209.42	841.68	1,052.11	-	2,735.48	420.84
Value	4	4,208.42	1,052.11	526.05	8,942.90	-	2,104.21	2,209.42	2,209.42	841.68	1,052.11	-	2,735.48	420.84
Value	5	4,208.42	1,052.11	526.05	8,942.90	-	2,104.21	2,209.42	2,209.42	841.68	1,052.11	-	2,735.48	420.84
Value	6	4,208.42	1,052.11	526.05	8,942.90	-	2,104.21	2,209.42	2,209.42	841.68	1,052.11	-	2,735.48	420.84
Value	7	4,208.42	1,052.11	526.05	8,942.90	-	2,104.21	2,209.42	2,209.42	841.68	1,052.11	-	2,735.48	420.84
Value	8	4,208.42	1,052.11	526.05	8,942.90	-	2,104.21	2,209.42	2,209.42	841.68	1,052.11	-	2,735.48	420.84
Value	9	4,208.42	1,052.11	526.05	8,942.90	-	2,104.21	2,209.42	2,209.42	841.68	1,052.11	-	2,735.48	420.84
Value	10	4,208.42	1,052.11	526.05	8,942.90	-	2,104.21	2,209.42	2,209.42	841.68	1,052.11	-	2,735.48	420.84
Date	1	01/10/2023​	09/04/1900​	09/04/1900​	24/03/1900​	00/01/1900​	09/04/1900​	09/04/1900​	09/04/1900​	09/04/1900​	09/04/1900​	00/01/1900​	09/04/1900​	09/04/1900​
Date	2	01/10/2023​	09/04/1900​	09/04/1900​	24/03/1900​	00/01/1900​	09/04/1900​	09/04/1900​	09/04/1900​	09/04/1900​	09/04/1900​	00/01/1900​	09/04/1900​	09/04/1900​
Date	3	01/10/2023​	09/04/1900​	09/04/1900​	24/03/1900​	00/01/1900​	09/04/1900​	09/04/1900​	09/04/1900​	09/04/1900​	09/04/1900​	00/01/1900​	09/04/1900​	09/04/1900​
Date	4	01/10/2023​	09/04/1900​	09/04/1900​	24/03/1900​	00/01/1900​	09/04/1900​	09/04/1900​	09/04/1900​	09/04/1900​	09/04/1900​	00/01/1900​	09/04/1900​	09/04/1900​
Date	5	01/10/2023​	09/04/1900​	09/04/1900​	24/03/1900​	00/01/1900​	14/02/1900​	00/01/1900​	00/01/1900​	09/04/1900​	00/01/1900​	00/01/1900​	00/01/1900​	00/01/1900​
Date	6	01/10/2023​	09/04/1900​	09/04/1900​	24/03/1900​	00/01/1900​	14/02/1900​	00/01/1900​	00/01/1900​	09/04/1900​	00/01/1900​	00/01/1900​	00/01/1900​	00/01/1900​
Date	7	01/10/2023​	09/04/1900​	09/04/1900​	24/03/1900​	00/01/1900​	14/02/1900​	00/01/1900​	00/01/1900​	09/04/1900​	00/01/1900​	00/01/1900​	00/01/1900​	00/01/1900​
Date	8	01/10/2023​	09/04/1900​	09/04/1900​	24/03/1900​	00/01/1900​	14/02/1900​	00/01/1900​	00/01/1900​	09/04/1900​	00/01/1900​	00/01/1900​	00/01/1900​	00/01/1900​
Date	9	01/10/2023​	09/04/1900​	09/04/1900​	24/03/1900​	00/01/1900​	14/02/1900​	00/01/1900​	00/01/1900​	09/04/1900​	00/01/1900​	00/01/1900​	00/01/1900​	00/01/1900​
Date	10	01/10/2023​	09/04/1900​	09/04/1900​	24/03/1900​	00/01/1900​	00/01/1900​	00/01/1900​	00/01/1900​	00/01/1900​	00/01/1900​	00/01/1900​	00/01/1900​	00/01/1900​

I am trying to create on seperate excel file a budget that tells me value of work that is complete every month in below format.

	01/10/2023​	01/11/2023​	02/10/2023​	01/11/2023​	03/10/2023​	01/11/2023​	04/10/2023​	01/11/2023​	05/10/2023​	01/11/2023​	06/10/2023​
	31/10/2023​	30/11/2023​	31/10/2023​	30/11/2023​	31/10/2023​	30/11/2023​	31/10/2023​	30/11/2023​	31/10/2023​	30/11/2023​	31/10/2023​
Design Fees
Warranty Costs
Site Setup
Prelims (note +2.77% monthly)
Substructure
Foundations Concrete
Blockwork
Subfloors
Piling
Upper Floors
Roof
Roof Coverings
Roof Trusess To Apartments s+f

Is there a formula to make this work?

Thanks in advance

Marty

 

[alansidman]

In the first column does Date1 equate to Value 1? Why are all the dates in column not in Column A for the year 1900 and how do they relate to the expected results. Without representative data that is meaningful, it is difficult to discern exactly what the expected results look like as all numbers and dates are the same.

 

[martydh3]

Hi Alan,

Yes Date 1 relates to value 1.

At the minute there is no dates are not populated but this will be completed in line with budget.

Oncve this is complete I want all figures to pull in to second excel file with budget for each calendar month between rows A & B.

Thanks

 

[martydh3]

Hi,

My data source has Dates and Values for elements of work in the same column.

Dataset

Type	Plot	Design Fees	Warranty Costs	Site Setup	Prelims (note +2.77% monthly)	Substructure	Foundations Concrete	Blockwork	Subfloors	Piling	Upper Floors	Roof	Roof Coverings	Roof Trusess To Apartments s+f
Value	1	4,208.42	1,052.11	526.05	8,942.90	-	2,104.21	2,209.42	2,209.42	841.68	1,052.11	-	2,735.48	420.84
Value	2	4,208.42	1,052.11	526.05	8,942.90	-	2,104.21	2,209.42	2,209.42	841.68	1,052.11	-	2,735.48	420.84
Value	3	4,208.42	1,052.11	526.05	8,942.90	-	2,104.21	2,209.42	2,209.42	841.68	1,052.11	-	2,735.48	420.84
Value	4	4,208.42	1,052.11	526.05	8,942.90	-	2,104.21	2,209.42	2,209.42	841.68	1,052.11	-	2,735.48	420.84
Value	5	4,208.42	1,052.11	526.05	8,942.90	-	2,104.21	2,209.42	2,209.42	841.68	1,052.11	-	2,735.48	420.84
Value	6	4,208.42	1,052.11	526.05	8,942.90	-	2,104.21	2,209.42	2,209.42	841.68	1,052.11	-	2,735.48	420.84
Value	7	4,208.42	1,052.11	526.05	8,942.90	-	2,104.21	2,209.42	2,209.42	841.68	1,052.11	-	2,735.48	420.84
Value	8	4,208.42	1,052.11	526.05	8,942.90	-	2,104.21	2,209.42	2,209.42	841.68	1,052.11	-	2,735.48	420.84
Value	9	4,208.42	1,052.11	526.05	8,942.90	-	2,104.21	2,209.42	2,209.42	841.68	1,052.11	-	2,735.48	420.84
Value	10	4,208.42	1,052.11	526.05	8,942.90	-	2,104.21	2,209.42	2,209.42	841.68	1,052.11	-	2,735.48	420.84
Date	1	01/10/2023	09/04/1900	09/04/1900	24/03/1900	00/01/1900	09/04/1900	09/04/1900	09/04/1900	09/04/1900	09/04/1900	00/01/1900	09/04/1900	09/04/1900
Date	2	01/10/2023	09/04/1900	09/04/1900	24/03/1900	00/01/1900	09/04/1900	09/04/1900	09/04/1900	09/04/1900	09/04/1900	00/01/1900	09/04/1900	09/04/1900
Date	3	01/10/2023	09/04/1900	09/04/1900	24/03/1900	00/01/1900	09/04/1900	09/04/1900	09/04/1900	09/04/1900	09/04/1900	00/01/1900	09/04/1900	09/04/1900
Date	4	01/10/2023	09/04/1900	09/04/1900	24/03/1900	00/01/1900	09/04/1900	09/04/1900	09/04/1900	09/04/1900	09/04/1900	00/01/1900	09/04/1900	09/04/1900
Date	5	01/10/2023	09/04/1900	09/04/1900	24/03/1900	00/01/1900	14/02/1900	00/01/1900	00/01/1900	09/04/1900	00/01/1900	00/01/1900	00/01/1900	00/01/1900
Date	6	01/10/2023	09/04/1900	09/04/1900	24/03/1900	00/01/1900	14/02/1900	00/01/1900	00/01/1900	09/04/1900	00/01/1900	00/01/1900	00/01/1900	00/01/1900
Date	7	01/10/2023	09/04/1900	09/04/1900	24/03/1900	00/01/1900	14/02/1900	00/01/1900	00/01/1900	09/04/1900	00/01/1900	00/01/1900	00/01/1900	00/01/1900
Date	8	01/10/2023	09/04/1900	09/04/1900	24/03/1900	00/01/1900	14/02/1900	00/01/1900	00/01/1900	09/04/1900	00/01/1900	00/01/1900	00/01/1900	00/01/1900
Date	9	01/10/2023	09/04/1900	09/04/1900	24/03/1900	00/01/1900	14/02/1900	00/01/1900	00/01/1900	09/04/1900	00/01/1900	00/01/1900	00/01/1900	00/01/1900
Date	10	01/10/2023	09/04/1900	09/04/1900	24/03/1900	00/01/1900	00/01/1900	00/01/1900	00/01/1900	00/01/1900	00/01/1900	00/01/1900	00/01/1900	00/01/1900

I am trying to create on seperate excel file a budget that tells me value of work that is complete every month in below format.

	01/10/2023	01/11/2023	02/10/2023	01/11/2023	03/10/2023	01/11/2023	04/10/2023	01/11/2023	05/10/2023	01/11/2023	06/10/2023
	31/10/2023	30/11/2023	31/10/2023	30/11/2023	31/10/2023	30/11/2023	31/10/2023	30/11/2023	31/10/2023	30/11/2023	31/10/2023
Design Fees
Warranty Costs
Site Setup
Prelims (note +2.77% monthly)
Substructure
Foundations Concrete
Blockwork
Subfloors
Piling
Upper Floors
Roof
Roof Coverings
Roof Trusess To Apartments s+f

Is there a formula to make this work?

Thanks in advance

Marty