Complex Sumifs Scenario?

Question

I would like to calculate orders based on two criteria:

• Resource Code (In column B)
• Depot (In row 1)

In the example below I want to return total orders in cell R5 for resource code "R0015" and depot "Folkestone". Answer should be 120 (40 + 80).

I have tried SUMIFS() but can't get it working on any depots with two columns, such as Folkestone in this example. Any help appreciated.

Answer 1

You can use SUMPRODUCT

=SUMPRODUCT((I4:N28)*(B4:B28=R3)*(I3:N3=R4))

Like this you can set conditions for the Resource Code (B4:B28) and the depost (I3:N3)

Answer 2

Based on your Excel Version use either SUM() or SUMPRODUCT() the following:

---

=SUM((R4=I3:N3)*(B4:B19=R3)*I4:N19)