Countifs

[cpg84]

I am trying to use COUNTIFS in my spreadsheet.

I have 6 teams in AA3:AA8 and another column that lists the winner of each game (AL:AL) they play against each other, and another with the loser of the same game (AR:AR).

What I want to do is use COUNTIF to see how many wins each team has against each other. This is the formula I am using, but it isn't working:

=COUNTIFS(AL:AL,AA3,AR:AR,$AA$3:$AA$8)

I want it to find when the team in AA3 appears anywhere in AL:AL and any of the other teams appear in AR:AR.

Hope this is explained good enough.

Thanks

 

[Aladin Akyurek]

I am trying to use COUNTIFS in my spreadsheet.

I have 6 teams in AA3:AA8 and another column that lists the winner of each game (AL:AL) they play against each other, and another with the loser of the same game (AR:AR).

What I want to do is use COUNTIF to see how many wins each team has against each other. This is the formula I am using, but it isn't working:

=COUNTIFS(AL:AL,AA3,AR:AR,$AA$3:$AA$8)

I want it to find when the team in AA3 appears anywhere in AL:AL and any of the other teams appear in AR:AR.

Hope this is explained good enough.

Thanks

Try...

=SUMPRODUCT(COUNTIFS(AL:AL,AA3,AR:AR,$AA$3:$AA$8))

 

[cpg84]

Thanks

I tried that, but for some reason, it still returns "0".

I thought that formula would have worked (and you have never steered me wrong before Aladin )

 

[Aladin Akyurek]

Thanks

I tried that, but for some reason, it still returns "0".

I thought that formula would have worked (and you have never steered me wrong before Aladin )

It works with me. Team names in AA3:AA8 should math exactly those in AL and in AR. Watch out for spaces around the entries.

 

[cpg84]

I'll have a closer look at it and let you know.

 

[Aladin Akyurek]

I'll have a closer look at it and let you know.

Here is a sample...

FAD

KAD

FAD

1

LAD

FAD

KAD

0

LAD

KAD

LAD

2

<colgroup><col style="width: 48pt;" span="2" width="64"> <col style="width: 24pt; mso-width-source: userset; mso-width-alt: 1137;" width="32"> <col style="width: 27pt; mso-width-source: userset; mso-width-alt: 1280;" width="36"> <col style="width: 25pt; mso-width-source: userset; mso-width-alt: 1166;" width="33"> <col style="width: 23pt; mso-width-source: userset; mso-width-alt: 1080;" span="2" width="30"> <col style="width: 22pt; mso-width-source: userset; mso-width-alt: 1052;" width="30"> <col style="width: 21pt; mso-width-source: userset; mso-width-alt: 995;" width="28"> <col style="width: 22pt; mso-width-source: userset; mso-width-alt: 1052;" width="30"> <col style="width: 20pt; mso-width-source: userset; mso-width-alt: 967;" width="27"> <col style="width: 48pt;" width="64"> <col style="width: 23pt; mso-width-source: userset; mso-width-alt: 1080;" width="30"> <col style="width: 20pt; mso-width-source: userset; mso-width-alt: 938;" width="26"> <col style="width: 19pt; mso-width-source: userset; mso-width-alt: 910;" width="26"> <col style="width: 19pt; mso-width-source: userset; mso-width-alt: 881;" width="25"> <col style="width: 22pt; mso-width-source: userset; mso-width-alt: 1052;" width="30"> <col style="width: 48pt;" width="64"> <tbody>
</tbody>