COUNTIF With Multiple Criteria From Different Ranges

[NTLKnight]

I am trying to calculate how many "WINS" a player has against how many "GAMES" they played. Figuring out how to calculate how many "GAMES" they played was relatively easy, I used this formula:

=COUNTIF({$K$3:$K$10,$K$12:$K$19,$R$3:$R$10,$R$12:$R$19,$Y$3:$Y$10,$Y$12:$Y$19,$AF$3:$AF$10,$AF$12:$AF$19,$AM$3:$AM$10,$AM$12:$AM$19},C3)

Here is a picture to help make sense of what I am doing...
Link to the image of my spreadsheet since the image file was too big to attach in this post)

I've been trying for 3 days, but I have not been able to figure out how to calculate the "WINS" part. I am using COUNTIFS for multiple criteria, but it only works when the name of the player is 1st on the list of the range (of 4 players). Unless the name is the first in the list of 4, it won't calculate a win. Here is the formula I am working with:

=COUNTIFS($K$3:$K$6,$C$3,$O$3:$O$6,1)+COUNTIFS($K$7:$K$10,$C$3,$O$7:$O$10,1)+COUNTIFS($K$12:$K$15,$C$3,$O$12:$O$15,1)+COUNTIFS($K$16:$K$19,$C$3,$O$16:$O$19,1)+COUNTIFS($R$3:$R$6,$C$3,$V$3:$V$6,1)+COUNTIFS($R$7:$R$10,$C$3,$V$7:$V$10,1)+COUNTIFS($R$12:$R$15,$C$3,$V$12:$V$15,1)+COUNTIFS($R$16:$R$19,$C$3,$V$16:$V$19,1)+

This formula works fine if the first name in the range coincides with the cell it is pointing to for the name, but if the name is the 2nd, 3rd, or 4th in the list...it returns a 0.

Please help!

Thank you,
Nathan

 

[Alex Blakenburg]

Most of us avoid merged cells like the plague. Someone might be able to come up with a better solutions but in the meantime how about something like this.
You are already using a helper column eg O, V etc. It looks like you also have merged cells in that.
What about unmerging those cells and putting in a formula to carry the 1 and 0s down and then using a Sumifs ?
I have only done the first 2 sumifs as an example below.

20240408 Countifs Win Loss Grid NTLKnight.xlsx
	K	L	M	N	O	P	Q	R	S	T	U	V	W
1
2	GAME 1			W/L	Helper			GAME 1			W/L	Helper
3	Nathan			LOSS	0			Nathan			WIN	1
4	Kurt				0			Kurt				1
5	Rick				0			Rick				1
6	Noah				0			Noah				1
7	Neetin			WIN	1			Neetin			LOSS	0
8	Joe				1			Joe				0
9	Tania				1			Tania				0
10	Alex				1			Alex				0
11	GAME 1			W/L				GAME 1			W/L
12	Neetin			WIN	1			Nathan			WIN	1
13	Kurt				1			Kurt				1
14	Rick				1			Rick				1
15	Noah				1			Noah				1
16	Neetin			LOSS	0			Neetin			LOSS	0
17	Joe				0			Joe				0
18	Tania				0			Tania				0
19	Alex				0			Alex				0
20
21								Name	Played	Wins	Ratio
22								Nathan		2
23								Rick		3
Sheet1

Cell Formulas
Range	Formula
O3:O10,V12:V19,O12:O19,V3:V10	O3	=IF(N3="W/L","",IF(N3="",O2,IF(N3="WIN",1,0)))
T22:T23	T22	=SUMIFS($O$3:$O$19,$K$3:$K$19,$R22)+SUMIFS($V$3:$V$19,$R$3:$R$19,$R22)

 

[NTLKnight]

Wow, Alex!--thank you so much!--it worked like a charm!

After setting up the whole leaderboard with your new formula ideas, I'm a bit stuck now on getting the final section of the leaderboard to auto-sort (or order) the winners. Any chance you can help me with a suggestion on this?

Here's a picture of the leaderboard, to give you a visual:
Leaderboard Image

There are 2 things I'm trying to do:
1. Auto sort the "Win Ratio" metric (in the case of my spreadsheet, the numbers in column AX (which now can borrow their values from the U column). (The "Games Played", "Wins", "Player Name/Picture" would follow.)
2. Put players at the bottom of the leaderboard who played less than 5 games (regardless of their "Win Ratio"...though if there are more than 1 players who played less than 5 games, it would be nice to have them sorted by their Win Ratio...even though at the bottom of the leaderboard...though this is not necessary).

Thank you again for your time!--I really appreciate it!

Nathan

 

[Alex Blakenburg]

That is a very different question to the original post. Please create a new thread for that.