robgoldstein
Board Regular
- Joined
- Oct 26, 2013
- Messages
- 165
- Office Version
- 2019
- Platform
- Windows
I am trying to run some some counts of unique entries that include blank cells from row 2 until row 1000. For some reason the counts are coming up with excess numbers. The real confusing part is that the Club count is working properly, and I used the same formula for each just changing the reference column letter. Can any of you explain it?
The first is my page where I have the formulas and the 2nd is the page where I am trying to count unique entries.
The first is my page where I have the formulas and the 2nd is the page where I am trying to count unique entries.
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B1 | B1 | =SUMPRODUCT((TeamImport!C2:C1000<>"")/COUNTIF(TeamImport!C2:C1000,TeamImport!C2:C1000&"")) |
| B2 | B2 | =SUMPRODUCT((TeamImport!G2:G1000<>"")/COUNTIF(TeamImport!G2:G1000,TeamImport!G2:G1000&"")) |
| B3 | B3 | =SUMPRODUCT((TeamImport!J2:J1000<>"")/COUNTIF(TeamImport!J2:J1000,TeamImport!J2:J1000&"")) |
| B4 | B4 | =SUMPRODUCT((TeamImport!H2:H1000<>"")/COUNTIF(TeamImport!H2:H1000,TeamImport!H2:H1000&"")) |
| B5 | B5 | =COUNTA(GameImport!C:C)-1 |
| B6 | B6 | =COUNTA(Venues!A:A)-1 |
| League Link Uploader Template.xlsm | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | |||
| 1 | uploaderID | count | club | gender | ageGroup | group | roster | teamName | division | coachFirstName | ||
| 2 | 1 | 1 | Brampton Elite | Boys U14 | Brampton Elite Boys U14 | Brampton Elite Blue | One | Matthew | ||||
| 3 | 1 | 2 | Brampton Elite | Boys U12 | Brampton Elite Boys U12 | Brampton Elite Gray | One | Matthew | ||||
| 4 | 1 | 3 | Brampton Elite | Boys U12 | Brampton Elite Boys U12 | Brampton Elite White | Three | Matthew | ||||
| 5 | 1 | 4 | Brampton Elite | Boys U12 | Brampton Elite Boys U12 | Brampton Elite Yellow | Two | Sally | ||||
| 6 | 1 | 5 | Markham SC | Boys U12 | Markham SC Boys U12 | Markham SC Blue | One | Sally | ||||
| 7 | 1 | 6 | Markham SC | Boys U14 | Markham SC Boys U14 | Markham SC Blue | One | Sally | ||||
| 8 | 1 | 7 | Markham SC | Boys U12 | Markham SC Boys U12 | Markham SC Green | Two | Matthew | ||||
| 9 | 1 | 8 | Markham SC | Girls U12 | Markham SC Girls U12 | Markham SC Orange | Two | Matthew | ||||
| 10 | 1 | 9 | Markham SC | Girls U12 | Markham SC Girls U12 | Markham SC Red | One | Matthew | ||||
| 11 | 1 | 10 | Markham SC | Boys U12 | Markham SC Boys U12 | Markham SC Yellow | Three | Bryce | ||||
| 12 | 1 | 11 | Pickering FC | Boys U12 | Pickering FC Boys U12 | Pickering FC Blue | Two | Bryce | ||||
| 13 | 1 | 12 | Pickering FC | Boys U12 | Pickering FC Boys U12 | Pickering FC Gray | One | Steve | ||||
| 14 | 1 | 13 | Pickering FC | Boys U12 | Pickering FC Boys U12 | Pickering FC White | Three | Paul | ||||
| 15 | 1 | 14 | Pickering FC | Boys U14 | Pickering FC Boys U14 | Pickering FC White | One | Andew | ||||
| 16 | 1 | 15 | SC Toronto | Boys U12 | SC Toronto Boys U12 | SC Toronto Gray | Two | Sally | ||||
| 17 | 1 | 16 | SC Toronto | Boys U12 | SC Toronto Boys U12 | SC Toronto Navy | One | Craig | ||||
| 18 | 1 | 17 | SC Toronto | Girls U12 | SC Toronto Girls U12 | SC Toronto Orange | Two | Craig | ||||
| 19 | 1 | 18 | SC Toronto | Girls U12 | SC Toronto Girls U12 | SC Toronto Purple | One | Craig | ||||
| 20 | 1 | 19 | SC Toronto | Boys U12 | SC Toronto Boys U12 | SC Toronto Red | Three | Craig | ||||
| 21 | 1 | 20 | ||||||||||
| 22 | 21 | |||||||||||
| 23 | 22 | |||||||||||
| 24 | 23 | |||||||||||
| 25 | 24 | |||||||||||
| 26 | 25 | |||||||||||
TeamImport | ||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| C22:C26,C2:C20 | C2 | =IFNA(LOOKUP(2,1/((SEARCH(Clubs!$A$2:$A$10,H2))*(Clubs!$A$2:$A$10<>"")),Clubs!$A$2:$A$10),"") |
| B3:B26 | B3 | =B2+1 |
| G2:G26 | G2 | =C2&" "&F2 |
