How To Count Unique Values in COL A Subject for each unique value in COL B ??

**amirtehrani** · 06-05-2012, 12:54 AM

This is my 1st post on this forum..
How To Count Unique Values in COL A Subject for each unique value in COL B ??

My COL A is Date
My COL B is Text (Driver)
My COL C is $Amount,

Would like to find out for each unique value in COL B
How Many Uniqe days he has worked in COL A ??
Members of COL B ( Drivers ) get a daily base wage for every day that they work ..
( in any day that they work they could be doing many number of jobs )
this is why I need to find out the unique number of days ( COL A ) that each unique Driver (COL B ) has worked ??

Once I get this part sorted I can easily Find out how much to pay for
and later on work out their pay and commission ..
Harderst thing at this stage for me is the 1st part ..

Please help me ..

Regards
Amir

**MarvinP** · 06-05-2012, 01:07 AM

Hi Amir and welcome to the forum,

See the attached where there are 2 Pivot Tables that might solve most of your problem. As for commissions above a certain hour mark, I'd need to deal with that in another try. Let us know if Pivot Tables are any help.

**amirtehrani** · 06-05-2012, 07:23 AM

Hi Marvin
Thank you for your table.
I it does solve most of my problem.
The driver get paid for every day that turn up for work.
Still need to figure out how to count the rows between drivers (AA,BB,CC,DD,... )
AA only worked 5 days but how do I get this 5 from the table ??
Amir From Sydney ..

**mohd9876** · 06-05-2012, 07:54 AM

Check the attachment.

**Marcol** · 06-05-2012, 08:32 AM

Okay, maybe a bit late in joining in, but see if this helps, at least for stage 1.
I have hidden the helper columns K:L with a grouping button, helper columns save using array entered formulae.

**MarvinP** · 06-05-2012, 11:57 AM

Hey - you are never too late with a good answer.

Here is a Pivot Table that does a similar thing using 2 helper columns. Why can't I get my Helper 2 column in a single step? I'm not good enough with SumProduct or CSE

. I'd love to see a formula that didn't need helper1 and just gave helper2. See attached for count of days a driver works.

**Haseeb Avarakkan** · 06-06-2012, 03:00 AM

Since you are looking for unique DATES, try this with CTRL+SHIFT+ENTER,

=SUM(IF(FREQUENCY(IF(B$2:B$56=I2,A$2:A$56),A$2:A$56),1))

How To Count Unique Values in COL A Subject for each unique value in COL B ??

LinkBack

Thread Tools

Rate This Thread

Display

How To Count Unique Values in COL A Subject for each unique value in COL B ??

Re: How To Count Unique Values in COL A Subject for each unique value in COL B ??

Re: How To Count Unique Values in COL A Subject for each unique value in COL B ??

Re: How To Count Unique Values in COL A Subject for each unique value in COL B ??

Re: How To Count Unique Values in COL A Subject for each unique value in COL B ??

Re: How To Count Unique Values in COL A Subject for each unique value in COL B ??

Re: How To Count Unique Values in COL A Subject for each unique value in COL B ??

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions