# Best way to count the number of matches

#### justinr321

##### New Member
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?

### Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Hi, welcome to the board.

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

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

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"))

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

Perfect, thank you

Happy to help, thanks for the feedback

Replies
6
Views
254
Replies
0
Views
216
Replies
3
Views
172
Replies
3
Views
157
Replies
3
Views
324

1,196,344
Messages
6,014,727
Members
441,841
Latest member
Prabu_sanku

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back