SumProduct and Countif

[Nycole]

I have this and it works great!! BUT I need to add a countif into this. How
do I do that? I need to break this down to how many per shift per team.
=SUMPRODUCT(--(Sheet4!B1:B20000=A4),--(Sheet4!D120000>=TIME(0,0,0)),--(Sheet4!D120000<=TIME(7,59,59)))

Entry Number Est. Date of Arrival Created On Port of Entry Team
4093775-2 1/2/2008 12/10/2007 0:00 12:45:49 PM 0708-010 C
8707657-1 1/1/2008 1/1/2008 0:00 2:38:13 PM 0708-010 T
8781871-7 1/1/2008 1/1/2008 0:00 5:07:51 PM 0708-010 X
8781872-5 1/1/2008 1/1/2008 0:00 5:16:28 PM 0708-010 X
8781873-3 1/1/2008 1/1/2008 0:00 6:20:48 PM 0708-010 X
8781874-1 1/1/2008 1/1/2008 0:00 6:25:11 PM 0708-010 X

 

[Conan Kelly]

Nycole,

How about adding a column to the table called "Shift". You can use formula
with the IF() or VLOOKUP() functions to assign a shift.

Then I would suggest using a pivot table. In the pivot table, you could
list your shifts in the first column and team in the second column (or vice
versa) making them row headers, and count one of the columns in the data
section (I think the data items need to be numeric...so one of your
date/time colums would work). And then you could break it down even further
by draging your Date of Arrival column to the Column Fileds area so you
could see the numbers accros the time series.

The pivot table could make your data look like this:
,,Date1,Date2,Date3
Shift1,Team1,5,10,15
,Team2,2,4,6
,Team3,3,6,9
Shift2,Team1,4,8,12
,Team2,1,3,5
,Team3,4,8,6

....or...

,,Date1,Date2,Date3
Team1,Shift1,2,4,6
,Shift2,1,3,5
Team2,Shift1,3,6,9
,Shift2,23,65,8
Team3,Shift1,3,54,87
,Shift2,5,897,23

(both of those examples are in comma-delimited format...copy and paste into
XL, then select all cells and click Data menu > Text to Columns... to see
what your results could look like).

Look up pivot table in XL's help. If you still need help, please write
back.

HTH,

Conan

 

[Nycole]

Sorry but I am even more confused than I was. How do I use the if, vlookup
for the shift?

 

[Conan Kelly]

Nycole,

What do you want for the names of your shifts? Will "Shift1", "Shift2",
etc... work? Or would you rather have just a number?

How many shifts? In your formula, you are testing for times between
midnight & 8 am (7:59:59 to be more precise), so I'm guessing 3 shifts in a
day.

By the looks of your formula you posted, your data starts in row 1 on sheet
4 (no column labels?). If the data in column D is stored as actual
date/time or time data (not stored as text), then create a new column after
the last column then you could use a formula like this to define shifts:

=IF(D1-INT(D1)>=TIME(16,0,0),"Shift2",IF(D1-INT(D1)>=TIME(8,0,0),"Shift1","Shift3"))

(Shift1 = 8 am - 4 pm, Shift2 = 4 pm - Midnight, Shift3 = Midnight - 8 am)

copy that formula down to the last row of your data

If you have column labels in row 1 and data starts in row 2, use the same
formula but change all of the "D1"s to "D2"s

HTH,

Conan

 

[Nycole]

Thank you that worked

Nycole,

What do you want for the names of your shifts? Will "Shift1", "Shift2",
etc... work? Or would you rather have just a number?

How many shifts? In your formula, you are testing for times between
midnight & 8 am (7:59:59 to be more precise), so I'm guessing 3 shifts in a
day.

By the looks of your formula you posted, your data starts in row 1 on sheet
4 (no column labels?). If the data in column D is stored as actual
date/time or time data (not stored as text), then create a new column after
the last column then you could use a formula like this to define shifts:

=IF(D1-INT(D1)>=TIME(16,0,0),"Shift2",IF(D1-INT(D1)>=TIME(8,0,0),"Shift1","Shift3"))

(Shift1 = 8 am - 4 pm, Shift2 = 4 pm - Midnight, Shift3 = Midnight - 8 am)

copy that formula down to the last row of your data

If you have column labels in row 1 and data starts in row 2, use the same
formula but change all of the "D1"s to "D2"s

HTH,

Conan

 

[Conan Kelly]

Nycole,

Did you use the pivot table? Do you have everything the way you want it?

Let me know if you need any more help,

Conan