Count of Unique Dates

[lsteffen]

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

 

[barry houdini]

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]

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]

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]

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

 

[lsteffen]

Thank you everyone. This was the answer I was looking for.

Liz