SOLVED

Matrix Search? Summing up values with defined criteria

Copper Contributor

Hello Excel forum,

 

I am looking for a maybe quite complex formula (or I think too complex :) ) and hope someone can help me.

 

Can you tell me how to make a sum of values depending on several criteria? So the formula should form the sum of the Overtimes as well as the sum of the Vacation for the several Workers below.

 

I tried with "lookup", "index", "countif", "sumif", etc...but I assume to be a combination of formula.

Hoping you can help me to fill the yellow fields in the attached Excel file.

 

Thank you in advance.

 

Greetings

Tobias

2 Replies
best response confirmed by TobiasSchneider (Copper Contributor)
Solution

@TobiasSchneider 

In B37:

=SUMPRODUCT(($A$2:$O$32=B$36)*($A$1:$O$1=$A37))

Fill down, then to the right (or vice versa).

See the attached version.

Thank you very much. This formulae solved my issue :)
1 best response

Accepted Solutions
best response confirmed by TobiasSchneider (Copper Contributor)
Solution

@TobiasSchneider 

In B37:

=SUMPRODUCT(($A$2:$O$32=B$36)*($A$1:$O$1=$A37))

Fill down, then to the right (or vice versa).

See the attached version.

View solution in original post