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 :oops:
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

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,721
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
 

Forum statistics

Threads
1,141,154
Messages
5,704,612
Members
421,359
Latest member
Edwardvanschothorst

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top