How to sum multiple column values based on multiple date ranges and resolve into date range summary?

[Makehoe]

Hi excel help community,

I have a sheet that collects our planned scaffold tower quantities by type for each bridge we have with an associated date range of use.

I would like to sum scaffold type in use by month (see below) to enable us to produce a histogram of scaffold type over several years.

As we have 30 bridges over many years, I'm trying to avoid multiple nested if functions.

What is the most efficient formula to rationalize the data?

[TABLE="width: 1117"]
<colgroup><col><col><col span="6"><col><col span="2"><col span="4"></colgroup><tbody>[TR]
[TD="colspan: 8"]Current Data[/TD]
[TD][/TD]
[TD="colspan: 6"]Seeking to produce from it[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]COL A[/TD]
[TD]COL B[/TD]
[TD]COL C[/TD]
[TD]COL D[/TD]
[TD]COL E[/TD]
[TD]COL F[/TD]
[TD]COL G[/TD]
[TD][/TD]
[TD][/TD]
[TD]COL A[/TD]
[TD]COL B[/TD]
[TD]COL C[/TD]
[TD]COL D[/TD]
[TD]COL E[/TD]
[/TR]
[TR]
[TD] ROW 1[/TD]
[TD]Scaffold Type[/TD]
[TD]Bridge 1 QTY[/TD]
[TD]Bridge 1 Start[/TD]
[TD]Bridge 1 Finish[/TD]
[TD]Bridge 2 QTY[/TD]
[TD]Bridge 2 Start[/TD]
[TD]Bridge 2 Finish[/TD]
[TD][/TD]
[TD] ROW 1[/TD]
[TD]Scaffold Type[/TD]
[TD="align: right"]Mar-19[/TD]
[TD="align: right"]Apr-19[/TD]
[TD="align: right"]May-19[/TD]
[TD="align: right"]Jun-19[/TD]
[/TR]
[TR]
[TD] ROW 2[/TD]
[TD]Type 1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]Mar-19[/TD]
[TD="align: right"]Apr-19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] ROW 2[/TD]
[TD]Type 1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] ROW 3[/TD]
[TD]Type 2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]May-19[/TD]
[TD="align: right"]Jun-19[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]Mar-19[/TD]
[TD="align: right"]Jun-19[/TD]
[TD][/TD]
[TD] ROW 3[/TD]
[TD]Type 2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD] ROW 4[/TD]
[TD]Type 3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]Apr-19[/TD]
[TD="align: right"]May-19[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]Apr-19[/TD]
[TD="align: right"]Jun-19[/TD]
[TD][/TD]
[TD] ROW 4[/TD]
[TD]Type 3[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD] ROW 5[/TD]
[TD]Type 4[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]May-19[/TD]
[TD="align: right"]Jun-19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] ROW 5[/TD]
[TD]Type 4[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD] ROW 6[/TD]
[TD]Type 5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] ROW 6[/TD]
[TD]Type 5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] ROW 7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] ROW 7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[RasGhul]

Hi

Can you use this?

It seems to produce the correct results but will be harder to implement if you have 30 bridges of data spanning across your sheet.

Book1
	A	B	C	D	E	F	G
1	Scaffold Type	Bridge 1 QTY	Bridge 1 Start	Bridge 1 Finish	Bridge 2 QTY	Bridge 2 Start	Bridge 2 Finish
2	Type 1	2	Mar-19	Apr-19
3	Type 2	4	May-19	Jun-19	4	Mar-19	Jun-19
4	Type 3	1	Apr-19	May-19	2	Apr-19	Jun-19
5	Type 4	9	May-19	Jun-19
6	Type 5
7
8		Mar-19	Apr-19	May-19	Jun-19
9	Type 1	2	2
10	Type 2	4		4	8
11	Type 3		3	1	2
12	Type 4			9	9
13	Type 5
Sheet1

Cell Formulas
Range	Formula
C8	=EOMONTH(B8,0)+1
D8	=EOMONTH(C8,0)+1
E8	=EOMONTH(D8,0)+1
B9	=SUMPRODUCT(($A$2:$A$6=$A9)*($C$2:$D$6>=B$8)*($C$2:$D$6<=EOMONTH(B$8,0))*$B$2:$B$6)+SUMPRODUCT(($A$2:$A$6=$A9)*($E$2:$G$6>=B$8)*($E$2:$G$6<=EOMONTH(B$8,0))*$E$2:$E$6)

 

[Makehoe]

Thanks RasGhul, however it'll be a very long string for the 30 bridges producing it this way.

Can anyone advise a neat solution?

 

[RasGhul]

Hi Make,

Does the scaffold types exceed 5?

Also does the Bridge qty/start/finish repeat for all 30 bridges?

 

[RasGhul]

Another possible solution is to transpose your data and convert your bridges table so that you can use a much smaller sumifs formula. This can be setup on the same sheet or a different sheet.

Mock up sheet link below with 30 bridges and calendar year. The calendar months are dynamic by entering the start date of the new year start.

Note to avoid deleting rows in your Bridges data as the transpose formula will result in an error.


https://www.dropbox.com/s/1lmkzox3r3ewa9u/sum multiple columns_and date ranges_2_makehoe.xlsx?dl=0