[Solved] Match category name for SUMPRODUCT

[youni]

Hello, dear users! I have a document in which I compare cities by criteria and criteria prices (importance).
For example criteria "Money" has importance 900 points and New York is very good city for earning money, so New York has quality assessment by criteria "Money" - 2 (very good city). And I multiply an importance of criteria to assessment: 900*2=1800. So, New York is very good for money.
There are other criteria, and I collect criteria into categories: Freedom, Security. And I wish to count result for each city.
But my formula in cell D10 does not satisfy me:
=SUMPRODUCT($C2:$C4,D2:D4)

choose_by_criteria_and_price_2025-03-13_14-16.png (50.66 KiB) Viewed 2493 times

My table shows, that Los Angeles is the best for Security reasons, and almost best for Freedom.
Here I placed exact ranges C2:C4; D2:D4. But when I add another criteria to category Freedom, I need to correct ranges in formula to: C2:C5; D2:D5.
I need new formula, that takes ranges by match criteria to category I already placed to resulting table: Freedom, Security.
I already can use function MATCH in B10, B11, but still have problem tuning it to my resulting cells D10:F11. Could you help me?
Which formula needs to be in D10 to get range C2:C4 by matching to category Freedom?

[MrProgrammer]

I need new formula, that takes ranges by match criteria to category I already placed to resulting table: Freedom, Security.

Just add the category to your SUMPRODUCT operands as shown in my attachment, sheet SUMPRODUCT. Note that cella A3, A4, A6, and A7 contain the appropriate category. However the value is hidden by Format → Conditional Formatting for range A2:A7. You don't need the MATCH function.

I do not recommend merging cells A2:A4. Merging, except in headings where the values are not used in formulas, causes trouble for people. If you do merge A2:A4 you must answer NO to the question about moving contents to the first cell, otherwise your formulas will fail. And you will have to unmerge if you want to make changes to A3 or A4, then re-merge. It is better to use conditional formatting to show only a single category for the set.

There are other criteria, and I collect criteria into categories: Freedom, Security. And I wish to count result for each city.

This task is much easier if you reorgainize your data so you can use a pivot table. Then Calc does all the work of creating the table and you don't need to write formulas for it. See my attachment, sheet Pivot.

If this solved your problem please go to your first post use the Edit ✏️ button and add [Solved] to the start of the Subject field. Select the green checkmark icon at the same time.

Calc Guide Chapter 8 - Using a Pivot Table   (formerly called Data Pilot)
[Tutorial] Ten concepts that every Calc user should know
[Tutorial] The SUMPRODUCT function

[youni]

Yes, you are right.
Your point of view to data structure is much better, but I need to use Google Sheets to present motivated choice of city to my boss.