SOLVED

Matrix Search? Summing up values with defined criteria

New 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

3 Replies
best response confirmed by TobiasSchneider (New 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

@TobiasSchneider 

Excel 365 Pro Plus with Power Pivot and Power Query.

Summing hours, instead of counting labels.

With PivotChart and Slicer.

No formulas, no VBA macro.

https://www.mediafire.com/file/fhj8vjjrm9eb4td/11_15_21.xlsx/file

https://www.mediafire.com/file/c29arhvpdyzdfue/11_15_21.pdf/file