Excel checking if teams have appeared in previous yeas

Hi all,
I wanted to check if the teams this year have played in all the five previous years. I've attached a spreadsheet to show you what I've done. What I'm doing isn't working which is why I'm asking for help. I've used the function below.

=SUMPRODUCT(--('7-8'!$D$2:$D$307=B2),--('8-9'!$D$2:$D$307=B2),--('9-10'!$D$2:$D$307=B2),--('10-11'!$D$2:$D$307=B2),--('11-12'!$D$2:$D$307=B2))

B2 would be the team names cell.

The thing is that I had it working only for the bottom 3 teams at one stage but I know there are more teams that qualify.

Any help gratefully received.

Regards to all.
Dave
Team-count-over-the-previous-5-y.xls
IrazorAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
NBVCConnect With a Mentor Commented:
You can use:

=COUNTIF('7-8'!$D$2:$D$307,B2)+COUNTIF('8-9'!$D$2:$D$307,B2)+COUNTIF('9-10'!$D$2:$D$307,B2)+COUNTIF('10-11'!$D$2:$D$307,B2)+COUNTIF('11-12'!$D$2:$D$307,B2)

This counts the number of time the name appears in all the sheets.

If you want to simply count if they appear at least once in any sheet, then you can add >0 to each countif

e.g

=(COUNTIF('7-8'!$D$2:$D$307,B2)>0)+(COUNTIF('8-9'!$D$2:$D$307,B2)>0)+(COUNTIF('9-10'!$D$2:$D$307,B2)>0)+(COUNTIF('10-11'!$D$2:$D$307,B2)>0)+(COUNTIF('11-12'!$D$2:$D$307,B2)>0)

This will tell you how many of the 5 sheets the name appears in.
0
 
IrazorAuthor Commented:
Excellent this is exactly what I wanted. Thanks NB_VC.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.