SanFelippo
Board Regular
- Joined
- Apr 4, 2017
- Messages
- 124
Hello,
So this is a bit difficult to explain, but I am going to do my best. I have 12 tabs for each month of the year (Jan, Feb, Mar, Apr, etc.), and then I have one more tab titled "Monthly Errors."
On the "Monthly Errors" tab, I have a column for each month starting in Column B. I then have in Column A the list of errors that could have occurred. I have an example of what one of the month tabs (i.e. Jan) looks like and what the "Monthly Errors" tab looks like at the bottom of this post. (We will use the "Jan" tab as the example in this, because all the month tabs are set up the exact same way).
How I need it to work:On the Jan tab, under the error column, there is going to be a drop down that will contain the following (The bolded text is the overall classification which you will find on the "Monthly Errors" Tab, while the items below each bolded text is what is in the drop down):
Ethn (App)
<tbody>
</tbody>
<tbody>
</tbody>
Race (Co-App)
<tbody>
</tbody>
On the Jan tab, someone is going to type in the ID number into Column C of the application they are documenting an error for, and then they are going to choose whatever error they found from the list I have provided above. This means that an application ID can appear more than once on the Jan tab, as multiple errors could have been found with it (Reference the examples I provided below to get an idea of what I am talking about).
(Using Jan for our example) I need a formula on the "Monthly Errors" Tab that will search column B on the Jan tab and come up with a count of how many errors occurred. The tricky part is this (and it's easiest to use an example to explain), if one ID number has 3 errors for lets say, Ethnicity of Applicant: 2, Ethnicity of Applicant: 3, Ethnicity of Applicant: 4, then on the "Monthly Errors" tab, under Ethn (App) for Jan, that would only count as 1 in the overall count of how many applications had an Ethn (App) error.
See the example below for a clear picture of how I need it to work. Can anyone help me out?
"Jan" Tab
<tbody>
</tbody>
So, on the "Monthly Errors" tab, the counts would be this:
"Monthly Errors" Tab
<tbody>
</tbody>
So this is a bit difficult to explain, but I am going to do my best. I have 12 tabs for each month of the year (Jan, Feb, Mar, Apr, etc.), and then I have one more tab titled "Monthly Errors."
On the "Monthly Errors" tab, I have a column for each month starting in Column B. I then have in Column A the list of errors that could have occurred. I have an example of what one of the month tabs (i.e. Jan) looks like and what the "Monthly Errors" tab looks like at the bottom of this post. (We will use the "Jan" tab as the example in this, because all the month tabs are set up the exact same way).
How I need it to work:On the Jan tab, under the error column, there is going to be a drop down that will contain the following (The bolded text is the overall classification which you will find on the "Monthly Errors" Tab, while the items below each bolded text is what is in the drop down):
Ethn (App)
| Ethnicity of Applicant: 1 | ||||||
| Ethnicity of Applicant: 2 | ||||||
| Ethnicity of Applicant: 3 | ||||||
| Ethnicity of Applicant: 4 | ||||||
| Ethnicity of Applicant: 5 | ||||||
Ethnicity of Applicant: Other Ethn (Co-App)
<tbody> </tbody> Race (App) |
<tbody>
</tbody>
| Race of Applicant: 1 |
| Race of Applicant: 2 |
| Race of Applicant: 3 |
| Race of Applicant: 4 |
| Race of Applicant: 5 |
| Race of Applicant: Amer. Indian/Principal Tribe |
| Race of Applicant: Other Asian |
| Race of Applicant: Other Pacific Islander |
<tbody>
</tbody>
Race (Co-App)
| Race of Co-Applicant: 1 |
| Race of Co-Applicant: 2 |
| Race of Co-Applicant: 3 |
| Race of Co-Applicant: 4 |
| Race of Co-Applicant: 5 |
| Race of Co-Applicant: Amer. Indian/Principal Tribe |
| Race of Co-Applicant: Other Asian |
| Race of Co-Applicant: Other Pacific Islander |
<tbody>
</tbody>
On the Jan tab, someone is going to type in the ID number into Column C of the application they are documenting an error for, and then they are going to choose whatever error they found from the list I have provided above. This means that an application ID can appear more than once on the Jan tab, as multiple errors could have been found with it (Reference the examples I provided below to get an idea of what I am talking about).
(Using Jan for our example) I need a formula on the "Monthly Errors" Tab that will search column B on the Jan tab and come up with a count of how many errors occurred. The tricky part is this (and it's easiest to use an example to explain), if one ID number has 3 errors for lets say, Ethnicity of Applicant: 2, Ethnicity of Applicant: 3, Ethnicity of Applicant: 4, then on the "Monthly Errors" tab, under Ethn (App) for Jan, that would only count as 1 in the overall count of how many applications had an Ethn (App) error.
See the example below for a clear picture of how I need it to work. Can anyone help me out?
"Jan" Tab
| B | C |
| Error | ID Number |
| Ethnicity of Applicant: 2 | 6666 |
| Ethnicity of Applicant: 3 | 6666 |
| Ethnicity of Applicant: 4 | 6666 |
| Race of Applicant: 1 | 7777 |
| Race of Applicant: 2 | 7777 |
| Ethnicity of Applicant: 1 | 8888 |
| Ethnicity of Applicant: 2 | 8888 |
| Race of Co-Applicant: 1 | 8888 |
| Race of Applicant: Amer. Indian/Principal Tribe | 9999 |
| Race of Applicant: Other Asian | 9999 |
| Ethnicity of Applicant: 1 | 1010 |
| Ethnicity of Applicant: 2 | 1010 |
| Race of Co-Applicant: Other Pacific Islander | 1010 |
| Race of Applicant: 5 | 1010 |
| Ethnicity of Co-Applicant: 1 | 1010 |
| Ethnicity of Co-Applicant: 2 | 1010 |
<tbody>
</tbody>
So, on the "Monthly Errors" tab, the counts would be this:
"Monthly Errors" Tab
| A | B | C | D | E | F | G | H | I | J | K | L | M |
| Error | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec |
| Ethn (App) | 3 | |||||||||||
| Ethn (Co-App) | 1 | |||||||||||
| Race (App) | 3 | |||||||||||
| Race (Co-App) | 2 |
<tbody>
</tbody>
