SUMPRODUCT & TRANSPOSE - Array

Jerseey

New Member
Joined
Apr 20, 2021
Messages
7
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: 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
Joined
Aug 18, 2015
Messages
11,867
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))
 
Solution
Master Excel Bundle

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.

Forum statistics

Threads
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?

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
Top