Formula To Count a Number of Occurences Only Once If They Have The Same ID Number

[SanFelippo]

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)

[TABLE="width: 364"]
<tbody>[TR]
[TD]Ethnicity of Applicant: 1
[/TD]
[/TR]
[TR]
[TD]Ethnicity of Applicant: 2
[/TD]
[/TR]
[TR]
[TD]Ethnicity of Applicant: 3
[/TD]
[/TR]
[TR]
[TD]Ethnicity of Applicant: 4
[/TD]
[/TR]
[TR]
[TD]Ethnicity of Applicant: 5
[/TD]
[/TR]
[TR]
[TD]
Ethnicity of Applicant: Other


Ethn (Co-App)

[TABLE="width: 364"]
<tbody>[TR]
[TD]Ethnicity of Co-Applicant: 1
[/TD]
[/TR]
[TR]
[TD]Ethnicity of Co-Applicant: 2
[/TD]
[/TR]
[TR]
[TD]Ethnicity of Co-Applicant: 3
[/TD]
[/TR]
[TR]
[TD]Ethnicity of Co-Applicant: 4
[/TD]
[/TR]
[TR]
[TD]Ethnicity of Co-Applicant: 5
[/TD]
[/TR]
[TR]
[TD]Ethnicity of Co-Applicant: Other
[/TD]
[/TR]
</tbody>[/TABLE]


Race (App)
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 364"]
<tbody>[TR]
[TD]Race of Applicant: 1
[/TD]
[/TR]
[TR]
[TD]Race of Applicant: 2
[/TD]
[/TR]
[TR]
[TD]Race of Applicant: 3
[/TD]
[/TR]
[TR]
[TD]Race of Applicant: 4
[/TD]
[/TR]
[TR]
[TD]Race of Applicant: 5
[/TD]
[/TR]
[TR]
[TD]Race of Applicant: Amer. Indian/Principal Tribe
[/TD]
[/TR]
[TR]
[TD]Race of Applicant: Other Asian
[/TD]
[/TR]
[TR]
[TD]Race of Applicant: Other Pacific Islander
[/TD]
[/TR]
</tbody>[/TABLE]


Race (Co-App)
[TABLE="width: 364"]
<tbody>[TR]
[TD]Race of Co-Applicant: 1
[/TD]
[/TR]
[TR]
[TD]Race of Co-Applicant: 2
[/TD]
[/TR]
[TR]
[TD]Race of Co-Applicant: 3
[/TD]
[/TR]
[TR]
[TD]Race of Co-Applicant: 4
[/TD]
[/TR]
[TR]
[TD]Race of Co-Applicant: 5
[/TD]
[/TR]
[TR]
[TD]Race of Co-Applicant: Amer. Indian/Principal Tribe
[/TD]
[/TR]
[TR]
[TD]Race of Co-Applicant: Other Asian
[/TD]
[/TR]
[TR]
[TD]Race of Co-Applicant: Other Pacific Islander
[/TD]
[/TR]
</tbody>[/TABLE]

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
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]B
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]Error
[/TD]
[TD]ID Number
[/TD]
[/TR]
[TR]
[TD]Ethnicity of Applicant: 2
[/TD]
[TD]6666
[/TD]
[/TR]
[TR]
[TD]Ethnicity of Applicant: 3
[/TD]
[TD]6666
[/TD]
[/TR]
[TR]
[TD]Ethnicity of Applicant: 4
[/TD]
[TD]6666
[/TD]
[/TR]
[TR]
[TD]Race of Applicant: 1
[/TD]
[TD]7777
[/TD]
[/TR]
[TR]
[TD]Race of Applicant: 2
[/TD]
[TD]7777
[/TD]
[/TR]
[TR]
[TD]Ethnicity of Applicant: 1
[/TD]
[TD]8888
[/TD]
[/TR]
[TR]
[TD]Ethnicity of Applicant: 2
[/TD]
[TD]8888
[/TD]
[/TR]
[TR]
[TD]Race of Co-Applicant: 1
[/TD]
[TD]8888
[/TD]
[/TR]
[TR]
[TD]Race of Applicant: Amer. Indian/Principal Tribe
[/TD]
[TD]9999
[/TD]
[/TR]
[TR]
[TD]Race of Applicant: Other Asian
[/TD]
[TD]9999
[/TD]
[/TR]
[TR]
[TD]Ethnicity of Applicant: 1
[/TD]
[TD]1010
[/TD]
[/TR]
[TR]
[TD]Ethnicity of Applicant: 2
[/TD]
[TD]1010
[/TD]
[/TR]
[TR]
[TD]Race of Co-Applicant: Other Pacific Islander
[/TD]
[TD]1010
[/TD]
[/TR]
[TR]
[TD]Race of Applicant: 5
[/TD]
[TD]1010
[/TD]
[/TR]
[TR]
[TD]Ethnicity of Co-Applicant: 1
[/TD]
[TD]1010
[/TD]
[/TR]
[TR]
[TD]Ethnicity of Co-Applicant: 2
[/TD]
[TD]1010
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


So, on the "Monthly Errors" tab, the counts would be this:



"Monthly Errors" Tab
[TABLE="class: grid, width: 600"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[TD]K
[/TD]
[TD]L
[/TD]
[TD]M
[/TD]
[/TR]
[TR]
[TD]Error
[/TD]
[TD]Jan
[/TD]
[TD]Feb
[/TD]
[TD]Mar
[/TD]
[TD]Apr
[/TD]
[TD]May
[/TD]
[TD]Jun
[/TD]
[TD]Jul
[/TD]
[TD]Aug
[/TD]
[TD]Sep
[/TD]
[TD]Oct
[/TD]
[TD]Nov
[/TD]
[TD]Dec
[/TD]
[/TR]
[TR]
[TD]Ethn (App)
[/TD]
[TD]3
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ethn (Co-App)
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Race (App)
[/TD]
[TD]3
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Race (Co-App)
[/TD]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Change A2:A4 of Monthy Errors to:

Ethnicity of Applicant
Ethnicity of Co-Applicant
Race of Applicant
Race of Co-Applicant

and in B2 control+shift+enter, not just enter, and copy down:

=SUM(IF(FREQUENCY(IF(ISNUMBER(SEARCH(A2,Jan!$B$2:$B$17)),Jan!$C$2:$C$17),Jan!$C$2:$C$17),1))

 

[SanFelippo]

I think this is just about what I need. However, it seems to be adding an additional 1 to the count for blank ID numbers. I know there shouldn't be a situation where you would have an error reported and you wouldn't have an ID attached to it, but for the sake of the count on the "Monthly Errors" tab not showing 1 for everything when there isn't anything filled out yet, could it be coded to not add to the count if the ID is blank?

 

[Aladin Akyurek]

I think this is just about what I need. However, it seems to be adding an additional 1 to the count for blank ID numbers. I know there shouldn't be a situation where you would have an error reported and you wouldn't have an ID attached to it, but for the sake of the count on the "Monthly Errors" tab not showing 1 for everything when there isn't anything filled out yet, could it be coded to not add to the count if the ID is blank?

Control+shift+enter:

=IF(A2="","",=SUM(IF(FREQUENCY(IF(ISNUMBER(SEARCH(A2,Jan!$B$2:$B$17)),Jan!$C$2:$C$17),Jan!$C$2:$C$17),1)))

Is this what you meant?

 

[SanFelippo]

I mean if there is a situation like this, where the error is selected but no ID is filled out on the Jan Tab, it is still showing a count of 1 on the Monthly Errors Tab (probably because it is recognizing the blank cell as a unique identifier), but if no ID is entered, then it should be 0. Like I said, this situation shouldn't really ever happen, but I am trying to make this idiot proof:


Monthly Errors Tab
[TABLE="class: cms_table_grid, width: 600"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[/TR]
[TR]
[TD]Error[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[TD]Sep[/TD]
[TD]Oct[/TD]
[TD]Nov[/TD]
[TD]Dec[/TD]
[/TR]
[TR]
[TD]Ethn (App)[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ethn (Co-App)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Race (App)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Race (Co-App)[/TD]
[/TR]
</tbody>[/TABLE]


Jan Tab
[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]Error[/TD]
[TD]ID Number[/TD]
[/TR]
[TR]
[TD]Ethnicity of Applicant: 2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ethnicity of Applicant: 3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ethnicity of Applicant: 4[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

In B2 of Monthly Errors, control+shift+enter, and copy down:

=SUM(IF(FREQUENCY(IF(ISNUMBER(SEARCH(A2,Jan!$B$2:$B$17)),IF(ISNUMBER(Jan!$C$2:$C$17),Jan!$C$2:$C$17)),Jan!$C$2:$C$17),1))

 

[SanFelippo]

Beautiful. Exactly what I needed. Thanks a bunch!

 

[Aladin Akyurek]

Beautiful. Exactly what I needed. Thanks a bunch!

You are welcome. Thanks for the update.