# 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

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

#### 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
64
Replies
4
Views
262
Replies
7
Views
149
Replies
5
Views
209
Replies
13
Views
694

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,578
Messages
5,770,948
Members
425,653
Latest member
UNSING

### 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.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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