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

mmansur04

New Member
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

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Matty

Well-known Member
Welcome to MrExcel!

Assuming 'Target' resides in H2, try:

Code:
``=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

Well-known Member
Nevermind, Matty beat me to it

AlphaFrog

MrExcel MVP
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

Last edited:

mmansur04

New Member

that worked perfectly,

thanks a lot!

mmansur04

New Member
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

Well-known Member

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

New Member
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

Well-known Member
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

Replies
0
Views
140
Replies
0
Views
316
Replies
4
Views
404
Replies
1
Views
64
Replies
6
Views
1K

1,136,321
Messages
5,675,063
Members
419,548
Latest member
wfarzand

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back