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.




3 Replies
best response confirmed by TobiasSchneider (New Contributor)


In B37:


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

See the attached version.

Thank you very much. This formulae solved my issue


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.