Search code examples
arraysgoogle-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-query

More compact or better way to write a function that SUM multiple COUNTIFS with same criteria


This is what my spreadsheet look like. I'm trying to make a database of certain Moba game.

I'm trying to write a function that counts a win and lose of specific Hero, a player character, of my team (Player 1 to 5 is always the same person)

Here is what I got

=SUM(COUNTIFS($F$9:$F$13,$L9,$E$9:$E$13,"Win"),
COUNTIFS($G$9:$G$13,$L9,$E$9:$E$13,"Win"),
COUNTIFS($H$9:$H$13,$L9,$E$9:$E$13,"Win"),
COUNTIFS($I$9:$I$13,$L9,$E$9:$E$13,"Win"),
COUNTIFS($J$9:$J$13,$L9,$E$9:$E$13,"Win"))

Now I'm wondering if there is any better or more compact way to write my function?

Link to google sheets


Solution

  • Yes you could do it by a sumproduct:

    =sumproduct(($E$9:$E$13=M$8)*($F$9:$J$13=$L9))