Count of Unique Dates

lsteffen

Board Regular
Joined
May 11, 2006
Messages
111
Hello All,

What I am trying to do is count once each time a unique date appears, but I need to match it by shift. For example, there are two entries for 01/03/05 for shift 1, but I want that counted only once. My end result will need to be Pieces per shift for the given month. Can someone help with a formula?

Here is some of my data:

ProdDate Shift Vol Pieces Hrs
01/03/06 1 187 0 0
01/03/06 1 98633 6251 5.5
01/03/06 2 91157 7957 6
01/04/06 1 203293 13130 8
01/04/06 2 182862 12819 8
01/05/06 1 3468 0 0
01/05/06 1 216456 16450 8
01/05/06 2 55816 4345 2
01/05/06 2 113952 13150 6
01/06/06 1 199668 10189 8
01/06/06 1 7072 0 0
01/06/06 2 188437 13624 8
01/07/06 1 216290 14543 8
01/09/06 1 164091 14514 8.

Thank you!
Liz :banghead:
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
To get total pieces for shift 1 for March 2006, assuming your data in columns A to E

=SUMPRODUCT(--(TEXT(A1:A100,"mmm yy")="Mar 06"),--(B1:B100=1),D1:D100)
 

lsteffen

Board Regular
Joined
May 11, 2006
Messages
111
Yes, it does give me the total pieces *for* shift 1, but not the pieces per *single* shift 1 for the month. What my calc was supposed to do is total the pieces and divide by how many unique shift 1's were for Jan 06. Can you help some more please?

Thank you!!!!

Liz
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,362
For a unique count of dates per shift, assuming that A1:E15 contains the data, maybe...

Let G2:G5 contain Jan 2006, Feb 2006, Mar 2006, etc.

Let H1 and I1 contain 1 and 2, repesenting Shift 1 and Shift 2

H2, copied down and across:

=COUNT(1/FREQUENCY(IF($B$2:$B$15=H$1,IF($A$2:$A$15-DAY($A$2:$A$15)+1=$G2,$A$2:$A$15)),IF($B$2:$B$15=H$1,IF($A$2:$A$15-DAY($A$2:$A$15)+1=$G2,$A$2:$A$15))))

...confirmed with CONTROL+SHIFT+ENTER. Alternatively, if you download and install the free add-in Morefunc.xll, you can use the following, more efficient formula...

=COUNTDIFF(IF($B$2:$B$15=H$1,IF($A$2:$A$15-DAY($A$2:$A$15)+1=$G2,$A$2:$A$15)),,FALSE)

...which also needs to be confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
What my calc was supposed to do is total the pieces and divide by how many unique shift 1's were for Jan 06.

Is all your data for a single month, in which case you don't have to test for the month. Assuming you have data for a longer period then I believe you need a combination of Domenic's suggestion and mine. Try

=SUM((TEXT(A2:A100,"mmm yy")="jan 06")*(B2:B100=1)*(D2:D100))/COUNT(1/FREQUENCY(IF((TEXT(A2:A100,"mmm yy")="jan 06")*(B2:B100=1),A2:A100),A2:A100))

confirmed with CTRL+SHIFT+ENTER
 

Watch MrExcel Video

Forum statistics

Threads
1,111,910
Messages
5,541,538
Members
410,547
Latest member
htran4
Top