SOLVED

New Contributor

# Matrix Search? Summing up values with defined criteria

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

# Re: Matrix Search? Summing up values with defined criteria

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.

# Re: Matrix Search? Summing up values with defined criteria

Thank you very much. This formulae solved my issue

# Re: Matrix Search? Summing up values with defined criteria

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