Count Customer Job only once when its job number is listed twice in a different column

[mmansur04]

Hello all,

I am hoping to get help with this equation I have been researching unsuccessfully for a while now.

What I have is:

Column A:The name of a company we did a "job" for.
Column F:Job number of each job recieved.

what I would like to do is make an equation that will Count the number of times a certain company is listed in column A, but I only want to count it once if the associated job number is listed two or more times in column F.

For example:

Say we did a job for "target". the specific job number is 2108. I only want to count the word "target" once if in column F, 2108 is listed two or three times.

We do multiple jobs for the same companies. So on a given day, we may have recorded that we did multiple different jobs for "target". This would result in different job numbers in column F, and multiple listings of "target in column A. But say in that given day we started a job for "target", stopped that job to work on something else, and then revisited the job later in the day. Since we record the exact times spent on each job throughout the day, the job we revisited would list "target" two times in column A, and list the corresponding job number two times in column F.

It may look somethings like this:

Target 8:00am-9:15 2108
Walmart 9:30 - 10:15 2459
Target 10:30- 10:45 2119
Macys 11:00- 12:00 2309
Target 1:00-3:00 2108
and so on....

Notice that target is listed 3 different times in what would be column A. However, In what would be column F, there are only 2 different job numbers listed, but one of them is listed twice (2108,2119,2108). What I want is my equation to list that we worked on two jobs for target, instead of 3.

Much appreciated,

thanks

 

[Matty]

Welcome to MrExcel!

Assuming 'Target' resides in H2, try:

=SUM(IF(FREQUENCY(IF(A$2:A$6=H2,MATCH(F$2:F$6,F$2:F$6,0)),ROW(A$2:A$6)-ROW(A$2)+1),1))

Array entered, i.e. with CTRL+SHIFT+ENTER.

Matty

 

[Weazel]

Nevermind, Matty beat me to it

 

[AlphaFrog]

Another one for what it's worth.

H2 = "Target"
Assumes the account numbers are numeric

=SUM((FREQUENCY(F2:F10,(A2:A10=H2)*F2:F10)>0)*1)-1

 

[mmansur04]

that worked perfectly,

thanks a lot!

 

[mmansur04]

Nevermind, Matty beat me to it

ok, now going along with that formula, say that I still wanted to count the number of times Target appears, but sometimes "Target" is not the only word that appears in H2.. maybe it will say "target minneapolis" I still want to count that cell. I know that I can use =countif(H : H , "target*), but how do I incorporate this into the above formula?

 

[Weazel]

If the values in column are still the same....target, walmart, etc. I'm not sure why you would want to put 'target minneapolis' as your search criteria.

If you had something like 'target minneapolis', 'target new york', etc in column A then you would need to use a wildcard to isolate target but not normally in the criteria

 

[mmansur04]

If the values in column are still the same....target, walmart, etc. I'm not sure why you would want to put 'target minneapolis' as your search criteria.

If you had something like 'target minneapolis', 'target new york', etc in column A then you would need to use a wildcard to isolate target but not normally in the criteria

ok, maybe that wasn't the best example. the reason I ask is because any given worksheet is edited by 3 different people. And they may use a different name for the same customer. say we did a job for the Minnesota Timberwolves. One person may have entered the information under the name "mn twolves", and then maybe later another person edits the worksheet entering a job for the Minnesota Timberwolves, but they may simply enter the information under "timberwolves". what I have been doing lately to count the number of times a job is done for the Timberwolves is this :

=COUNTIF(A7:A45, "timber*") + COUNTIF(A7:A45, "wolve*") + COUNTIF(A7:A45, "t wolve*") + COUNTIF(A7:A45, "t-wolv*") + COUNTIF(A7:A45, "twolv*") + COUNTIF(A7:A45, "mn t*")

in order to count every possible entry for the Minnesota Timberwolves. But when I realized that I only wanted to count a Job once if the job number is listed twice, thats when I asked the first question in the post. But now, since the entries are not consistent for every customer, I want to incorporate this into the formula we have been working with.

 

[Weazel]

that's alot of variations on the search criteria but referencing Matty's original formula you could potentially try something like....

=SUM(IF(FREQUENCY(IF(ISNUMBER(SEARCH(H2,$A$2:$A$6)),MATCH(F2:F6,F2:F6,0)),ROW(A2:A6)-ROW(A2)+1),1)) control shift enter

though I'm not sure how you could do it to cover all the criteria variations you have in the countif

essentially the search would just look at what you have in H2 and search column A. As it pertains to the original data...searching on 'tar' target' target minneapolis would all return true.

I imagine it might be possible to include a value you didnot intend to count if the search criteria was too broad