CODEWITHSUNDEEP
excelexcel-formula
Derik0003
Derik0003

Reputation: 67

Excel: How to calculate the sum product with multiple conditions?

My table looks like this: enter image description here

As you can see the there is a rate chart and distribution of Staff among different levels. What I need is a table with the total rate for each staff. For example, if you take staff C, you can see he is in Cat2 with 2 times in L1U and 3 times in L1L Cat2; also Cat1 for 3 times in L2L. So the total amount for C will be:

Level&Cat    Rate    Count    Subtotal
L1U-Cat2      15      2          30
L1L-Cat2      17      3          51
L2L-Cat1      35      3          105  == Total = 105+51+30 =>186

So the final output will be:

Staff Name       Total
A              Need calculation
B              Need calculation
C                 186
D              Need calculation
...                ....

How can this be possible? Now I am using Count if but for each row and column so it is almost like 51 columns long for each level and category and the calculation threading gives a lot of lag because of this.

What formula can I use?

Upvotes: 1

Views: 923

Answers (2)

Naresh
Naresh

Reputation: 3034

Without helper column

We can wrap Qualia's brilliant answer in one array (CSE - Control+Shift+Enter) function like below

=SUM(IF($G$3:$I$14=K3,INDEX($B$3:$D$12,N(IF(1,MATCH($F$3:$F$14,$A$3:$A$12,0))),N(IF(1,MATCH($G$2:$I$2,$B$2:$D$2,0))),1)))

As suggested by Mark Fitzpatrick in the comments below we can also use following formula.

=SUM(INDEX($B$3:$D$12,N(IF(1,MATCH($F$3:$F$14,$A$3:$A$12,0))),N(IF(1,MATCH($G$2:$I$2,$B$2:$D$2,0))),1)*($G$3:$I$14=K3))

Copy the formula. Press F2 in L3 cell. Paste the formula and press Control+Shift+Enter. Copy that cell to L4:L6

enter image description here

Explanation

We can create an array of K3:M14 in Qualia's answer using array function

=INDEX($B$3:$D$12,N(IF(1,MATCH($F$3:$F$14,$A$3:$A$12,0))),N(IF(1,MATCH($G$2:$I$2,$B$2:$D$2,0))),1)

Then we can wrap this in Sum(if..) as mentioned above.

Refer this Exceljet link for N(IF..) function.

Upvotes: 2

Qualia Communications
Qualia Communications

Reputation: 715

Here is a solution with three helper columns K:M.

enter image description here

Formulas

  • K3 =INDEX($A$2:$D$12,MATCH($F3,$A$2:$A$12,0),MATCH(G$2,$A$2:$D$2,0)). It looks up the rate for the given combination of Level and Category. It would not be necessary to use the MATCH formula, but it is more robust this way.
  • P3 =SUMIFS($K$3:$M$14,$G$3:$I$14,O3) just sums up the rates per each category for the given staff name. (Revised based on Naresh's comment)

Upvotes: 3

Related Questions