Hello, how could I go about using a range for both my source and criteria?
As an example:
My source (dubplicates are only counted as 1, list of completed courses by a user/unique email)
Criteria 1 (list of unique emails)
In Range B2 I'm trying to count the number of matches between my source and criteria 2 using the course number.
Criteria 2 (master list of courses)
In the above example the match/count would be 9.
There will be course codes completed by a user not on the master course list, so I wouldn't be able to count the number of unique completed courses by each unique email. I wish it was that simple. Each completed course needs to match the master list to count as 1.
Thank you!
As an example:
My source (dubplicates are only counted as 1, list of completed courses by a user/unique email)
| test.xlsx | ||||
|---|---|---|---|---|
| A | B | |||
| 1 | Course Number | |||
| 2 | 608400 | email@mymail.com | ||
| 3 | 608400B | email@mymail.com | ||
| 4 | 608400B | email@mymail.com | ||
| 5 | 608400B | email@mymail.com | ||
| 6 | 608400M2 | email@mymail.com | ||
| 7 | 608400M2B | email@mymail.com | ||
| 8 | 619152 | email@mymail.com | ||
| 9 | 619152B | email@mymail.com | ||
| 10 | 619169 | email@mymail.com | ||
| 11 | 619169B | email@mymail.com | ||
| 12 | 619178 | email@mymail.com | ||
| 13 | 619178B | email@mymail.com | ||
Sheet3 | ||||
Criteria 1 (list of unique emails)
| test.xlsx | ||||
|---|---|---|---|---|
| A | B | |||
| 1 | Count | |||
| 2 | email@myemail.com | |||
Sheet5 | ||||
Criteria 2 (master list of courses)
| 608400 |
| 608400B |
| 608400M2 |
| 608400M2B |
| 617256 |
| 617256B |
| 619149 |
| 619149B |
| 619152 |
| 619152B |
| 619155M6 |
| 619155M6B |
| 619167M1 |
| 619167M1B |
| 619167M2 |
| 619167M2B |
| 619169 |
| 619169B |
| 619170 |
| 619170B |
| 619178 |
| 619178B |
| 619303B |
| 718139M1 |
| 718139M1B |
| 718139M2 |
| 718139M2B |
In the above example the match/count would be 9.
| test.xlsx | |||
|---|---|---|---|
| A | |||
| 1 | 608400 | ||
| 2 | 608400B | ||
| 3 | 608400M2 | ||
| 4 | 608400M2B | ||
| 5 | 617256 | ||
| 6 | 617256B | ||
| 7 | 619149 | ||
| 8 | 619149B | ||
| 9 | 619152 | ||
| 10 | 619152B | ||
| 11 | 619155M6 | ||
| 12 | 619155M6B | ||
| 13 | 619167M1 | ||
| 14 | 619167M1B | ||
| 15 | 619167M2 | ||
| 16 | 619167M2B | ||
| 17 | 619169 | ||
| 18 | 619169B | ||
| 19 | 619170 | ||
| 20 | 619170B | ||
| 21 | 619178 | ||
| 22 | 619178B | ||
| 23 | 619303B | ||
| 24 | 718139M1 | ||
| 25 | 718139M1B | ||
| 26 | 718139M2 | ||
| 27 | 718139M2B | ||
Sheet2 | |||
There will be course codes completed by a user not on the master course list, so I wouldn't be able to count the number of unique completed courses by each unique email. I wish it was that simple. Each completed course needs to match the master list to count as 1.
Thank you!
