SUMPRODUCT returns same (wrong) value regardless of criteria

Hi everyone,

I’m trying to build a summary table for a PowerPoint chart in Excel. I have two sheets:

Sheet 1: “Powerpoint_r”

• Departments (e.g., “Department 1”, “Department 2”…)
• Services (e.g., “Service a”, “Service b”…)
• I want Revenue per department per service, and later aggregate by service groups

https://preview.redd.it/sxctiv8kd9hg1.png?width=676&format=png&auto=webp&s=ae2316ae89f847975486f271ac8e9f2e39ab6e6a

Sheet 2: “Summary”:

• Each department has multiple sub-division.
• Each sub-divisionhas 4 KPIs in columns: Revenue, Contribution Margin, CM %, Days
• There is a helper row across the KPI columns where I repeat the department name under each revenue column (so I can filter by department even though the headers are merged).

https://preview.redd.it/ir006fj3e9hg1.png?width=1440&format=png&auto=webp&s=b46e865befb7f68906b4a8fa818c8cf4cdfb5d58

My formula in the Powerpoint sheet (B4) is:

=SUMPRODUCT(

(Summary_r!$A$8:$A$10=$A4)*

(Summary_r!$C$6:$N$6=B$1)*

N(Summary_r!$C$8:$N$10)

)

Which gives me 300. However, the correct value would for Service a in Departement 1 would be 100 EUR + 499 EUR = 599 EUR

Even worse: if I change the service name in A4 (Service a / b / c), the result sometimes stays the same constant value, as if the service criteria is ignored.

Any ideas how I can solve this?

Thanks!