SUMPRODUCT & TRANSPOSE - Array

Jerseey

New Member
Joined
Apr 20, 2021
Messages
8
Office Version
  1. 365
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.

Cost per Department.png


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?

Cost per Department (Evaluation).png
 

Attachments

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

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Welcome to the forum!

How about:

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))
 
Upvote 0
Solution

Forum statistics

Threads
1,213,504
Messages
6,114,016
Members
448,543
Latest member
MartinLarkin

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?

Disable AdBlock

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
Back
Top