Best way to count the number of matches

[justinr321]

I have a list of 100 names, 1 = Fred, 2 = David

Assume this is a lottery, each week we draw 3 names and these are listed in a row on sheet 1 next to Y/N depending if they collect their prize, e.g.

Week | Name 1 | Prize 1 | Name 2 | Prize 2 | Name 3 | Prize 3
Week 1 | Fred | Y | David Y | Etc | N
Week 2 | Fred | N | David Y | Etc | N

On Sheet 2 I have a simple COUNTIF to show how many time Fred has won, e.g. =COUNTIF(Sheet1!B$2:AM$100,Fred)

Which would show 2 in this example, but how do I combine functions to show that Fred has 1 win, prize = N?

 

[FDibbins]

Hi, welcome to the board.

Change that to the countifS function - if you are using 2010 or later, it allows multiple criteria

 

[justinr321]

I've tried and failed with countifs - got any examples that match this use case or suggestions for how to reformat the data?

 

[Tetra201]

Try this formula (Note 1: to be entered using Ctrl+Shift+Enter, not just Enter; Note 2: lightly tested):

=SUM((Sheet1!$B$2:$AL$100="Fred")*(Sheet1!$C$2:$AM$100="N"))

 

[FDibbins]

Try this...

	D​	E​	F​	G​	H​	I​	J​	K​	L​	M​	N​
1​	Week	Name 1	Prize 1	Name 2	Prize 2	Name 3	Prize 3			Name	name & Y
2​	Week 1	Fred	Y	David	Y	Etc	N		Fred	2​	1​
3​	Week 2	Fred	N	David	Y	Etc	N		David	2​	2​
4​									Etc	0​	0​

M2=COUNTIF(E$2:J$3,$L2)
N2=COUNTIFS(E$2:J$3,$L2,$F$2:$K$3,"Y")+
both copied down.

Note the offset in the bolded criteria

 

[justinr321]

Perfect, thank you

 

[FDibbins]

Happy to help, thanks for the feedback