SUMPRODUCT & TRANSPOSE - Array

Jerseey

New Member
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?

Attachments

• Cost per Department (Evaluation).png
17.4 KB · Views: 5

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

Eric W

MrExcel MVP
Welcome to the forum!

Book2
ABCDEFGHIJ
1
2
3
4DepartmentLabor Category01-202102-2021203-20213Labor Category01-202102-2021203-20213
5Product 01Finance125Finance101112
6Product 02Sales236Sales202122
7Product 01Sales443Engineering303132
8
9Cost
10Department01-202102-2021203-20213
11Product 0190106126
12Product 024063132
Sheet8
Cell Formulas
RangeFormula
C11:E12C11=SUMPRODUCT(SUMIFS(H\$5:H\$7,\$G\$5:\$G\$7,\$B\$5:\$B\$7)*(\$A\$5:\$A\$7=\$A11)*(C\$5:C\$7))

Jerseey

New Member
Thanks Eric W, that works perfectly!

Eric W

MrExcel MVP
Happy to help! Thanks for the feedback.

Replies
2
Views
59
Replies
4
Views
252
Replies
7
Views
148
Replies
5
Views
204
Replies
13
Views
682

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,152,193
Messages
5,768,770
Members
425,492
Latest member
blueexcel123

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.

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

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