SUMPRODUCT & TRANSPOSE - Array

[Jerseey]

Hi,
I was hoping someone could help me find a way to avoid using an array formula for the following problem.

I have two tables:
Table 1: Table with hours per month, where rows are specified with department and labor category.
Table 2: Table with hourly rates per month, where rows are specified per labor category.

I have created a formula that povides me the cost per month per department, but the formula is an array formula which I really want to avoid if possible.

C11 = {SUMPRODUCT(((C$5:C$7)*TRANSPOSE((H$5:H$7))),(--($A$5:$A$7=$A11)*(($B$5:$B$7)=TRANSPOSE(($G$5:$G$7)))) )}

However, when I try to evaluate the formula by splitting it in the two parts, I'm able to use a normal SUMPRODUCT function without using an array formula. Why and is there a way to fix my formula to avoid making an array formula?

 

[Eric W]

Welcome to the forum!

How about:

Book2
	A	B	C	D	E	F	G	H	I	J
1
2
3
4	Department	Labor Category	01-2021	02-20212	03-20213		Labor Category	01-2021	02-20212	03-20213
5	Product 01	Finance	1	2	5		Finance	10	11	12
6	Product 02	Sales	2	3	6		Sales	20	21	22
7	Product 01	Sales	4	4	3		Engineering	30	31	32
8
9	Cost
10	Department		01-2021	02-20212	03-20213
11	Product 01		90	106	126
12	Product 02		40	63	132
Sheet8

Cell Formulas
Range	Formula
C11:E12	C11	=SUMPRODUCT(SUMIFS(H$5:H$7,$G$5:$G$7,$B$5:$B$7)*($A$5:$A$7=$A11)*(C$5:C$7))

 

[Jerseey]

Thanks Eric W, that works perfectly!

 

[Eric W]

Happy to help! Thanks for the feedback.