scheduling tool using SUMPRODUCT(SUMIF()) with arrays of different sizes

[JayEhWhy]

<colgroup><col><col span="3"><col><col><col></colgroup><tbody></tbody>

I am looking for some guidance on creating a scheduling tool.
What I have is a sheet of dates and job codes and a sheet with job codes and their duration.
I'd like to sum the duration of work in each month.

	Sheet1			Sheet2
	A	B			A	B
1	Code	Date		1	Code	Duration
2	1	1/18/2018		2	1	5
3	2	4/23/2018		3	2	10
4	3	1/22/2018		4	3	15
5	4	4/18/2017		5	4	20
6	5	1/22/2018		6	5	25
7	6	4/18/2017		7	6	30
8	1	4/9/2018
9	2	2/16/2018
10	3	4/25/2017

<colgroup><col><col span="3"><col><col><col></colgroup><tbody>
</tbody>

I have this working right now with the following code as long as the to arrays are the same size:
SUMPRODUCT(SUMIF('Sheet1'!A2:A7,'Sheet2'!A2:A7,'Sheet2'!B2:B7),(MONTH('Sheet1'!B2:B7)=4)*(YEAR('Sheet1'!B2:B7)=2017))

But as you can see Sheet1 is longer and will be changing in size.

Any ideas on making this work with different array sizes or an alternate approach?

 

[Marcelo Branco]

See if this does what you need

=SUMPRODUCT(SUMIF(Sheet2!$A$2:$A$7,Sheet1!A2:A10,Sheet2!$B$2:$B7),--(MONTH(Sheet1!B2:B10)=4),--(YEAR(Sheet1!B2:B10)=2017))

M.

 

[seguin85]

Would adding another column on sheet 1 be a possibility? For example in column C you could have duration and then do a lookup formula to bring in the duration of each code. Then on sheet 3 you could have a sumif formula to calculate this.

 

[barry houdini]

Is the Sheet2 table exactly as shown (whereby the codes and durations have a linear relationship), or is that just a simplified example?

If it's the former try this formula

=SUMPRODUCT('Sheet1'!A2:A100,(TEXT('Sheet1'!B2:B100,"mmmm yyyy")="April 2017")+0)*5

 

[JayEhWhy]

See if this does what you need

=SUMPRODUCT(SUMIF(Sheet2!$A$2:$A$7,Sheet1!A2:A10,Sheet2!$B$2:$B7),--(MONTH(Sheet1!B2:B10)=4),--(YEAR(Sheet1!B2:B10)=2017))

M.

Worked like a charm! Appreciate the help.
Can you explain why using the sheet2 Code first in the SUMIF made the difference?

 

[Marcelo Branco]

When Sheet1!A2:A10, a range with 9 rows, is used as the second argument of SUMIF it returns an array of equal size. In other words, SUMIF does the calculation (conditional sum) for each value in A2:A10 and returns an array of values - the result for each value present in A2:A10). In short, this array has the same size of the range A2:A10 and so SUMPRODUCT works properly.

On the other hand, when Sheet2!A2:A7, a range with 6 rows, is used as the second argument of SUMIF it returns an array with 6 values and SUMPRODUCT doesn't work correctly because the other arrays (Month and Year) have 9 values.

Hope i made myself clear

M.