•2 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
SUMPRODUCT returns same (wrong) value regardless of criteria
Our take
Are you frustrated with SUMPRODUCT returning the same incorrect value, regardless of your criteria? You’re not alone in grappling with this common Excel challenge. By leveraging AI-native spreadsheet technology, you can streamline your data analysis and ensure accurate results every time. In this guide, we’ll explore how to diagnose and resolve the issues in your formula, leading you to the correct revenue calculations for your PowerPoint chart. Dive in to unlock the insights you need for effective data-driven decision-making!
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
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).
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!
[link] [comments]
Read on the original site
Open the publisher's page for the full experience