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
Yes you could do it by a sumproduct:
=sumproduct(($E$9:$E$13=M$8)*($F$9:$J$13=$L9))