SumProduct - multiple criteria and IF question

[ronnomad]

I have a spread sheet (18,000 rows) with 4 columns (Location, Date, Time and
Dept.). I want some specific results but think I need two steps.

First - in a helper column convert the time column to a single digit number
indicating workshift. The workshift times are: 06:45:01 - 14:45:00; 14:45:01
- 22:45:00; 22:45:01 - 06:45:00 where 06:45:01 is first shift and so on.
This looks like it should be an IF statement but I'm not sure how to enter
the formula to show that if the time in the column is between the indicated
start & finish to show as 1, 2 or 3.

Second - once this helper column is created; in other sheets (one for each
shift), with column A duplicating the Dept numbers and columns B - M
indicating months, a SumProduct formula which identifies the number of
occurances for each Department by month.

Helpful, but not critical, would be to also identify the location (there are
2). I could duplicate the Depts on each sheet (there are only 46) if the
formula could have three variables (month, shift and location).

Thanks,

Ron R

 

[Roger Govier]

Hi

To convert the times to Shift, title column E Shift and in E2 enter
=IF(C2*24<7,1,IF(C2*24<15,2,3))

I would then use a Pivot Table to analyse the results.

For help on how to set up and manipulate data in a Pivot table take a
look at the following sites
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html

http://www.datapigtechnologies.com/flashfiles/pivot1.html

http://www.edferrero.com/Tutorials.aspx

 

[Pete_UK]

I think Roger's formula needs a bit of tweaking. Try this:

=IF(A1*24<=6.75,3,IF(A1*24<=14.75,1,IF(A1*24<=22.75,2,3)))

Note that 6.75 is equivalent to 6:45:00, 14.75 to 14:45:00 and 22.75 to
22:45:00.

Hope this helps.

Pete

 

[ronnomad]

Thanks guys,

That resolves the first part. Now waiting for the second.

Ron

 

[Pete_UK]

Presumably you don't want to take up Roger's suggestion of using a
pivot table. OK, it will be easier if you introduce some named ranges -
I assume you have these headings at the top of your columns in Sheet1:

Location, Date, Time, Dept and Shift.

Highlight from Location across to Shift and down the full extent of
your data and Insert | Name | Create, ensure Top Row is the only box
selected in the pop-up, then click OK.

Set up Sheet2 with the value 1 in A1 and the following headings in
row2:

Department, Location, January, February, March etc across to December

Put all your Departments twice in column A from A3 downwards, and your
locations in column B. Then enter this formula in C3:

=SUMPRODUCT((Dept=$A3)*(Location=$B3)*
(Shift=$A$1)*(MONTH(Date)=COLUMN()-2))

This is all one formula - I've split it here to avoid awkward
line-breaks. If this formula does not go into column C, you will need
to adjust the 2 at the end, eg if it is in column D then make it 3. The
formula can be copied across to cover up to December, then these 12
formulae can be copied down to cover your 46 departments twice.

Then you can copy the sheet twice, and change the number 1 in A1 to 2
for Shift 2 and to 3 for Shift 3 in your copied sheets.

This should give you what you want.

Hope this helps.

Pete

 

[Roger Govier]

Hi Pete

Nice solution for the not PT approach<bg>

However, if you change the formula to
=SUMPRODUCT((Dept=$A3)*(Location=$B3)*
(Shift=$A$1)*(MONTH(Date)=COLUMN(A1)))

then there will be no need to adjust -2 to -3 if data layout is
different

 

[Pete_UK]

Yeah, thanks Roger. I also thought afterwards that the OP might not
need three sheets as all he needs to do is change the shift number in
A1 and it will recalculate. I don't know how many years his data
covers, but he might want to introduce a year in B1, say, then the
formula could become:

=SUMPRODUCT((Dept=$A3)*(Location=$B3)*
(Shift=$A$1)*(MONTH(Date)=COLUMN(A1))*(YEAR(Date)=$B$1))

Not sure if he is still listening.

Pete

 

[ronnomad]

Pete, Roger,

Still listening. Sorry I haven't responded earlier but got sidetracked with
another issue. I misunderstood the original response regarding the pivot
table. Haven't used them much and thought it was just a way to get the first
half of the equation and Pete's looked easier to use. Will try the next half
of the solution some time today and respond. Since you asked, my data only
covers one calendar year (2006) but I may combine parts of 2006 & 2007 for a
fiscal analysis so, the addition to the formula will be an added benefit.

Thanks again guys.

Ron R.

 

[ronnomad]

Pete, Roger,

Well, the good news is the formula works. Wrote it, copied right and down
and got the results I was looking for. I am going to set up a separate sheet
for each shift although I do see that by changing the reference in A1 I can
get the sheet to recalculate.

The bad news is that I don't understand why. I comprehend all the
references in the formula with the exception of the Date portion. I don't
see how the reference to the Column(A1) is getting the date data when Column
A in this sheet has the Shift reference in A1 and the column itself has
Department in A2 and the Departments in A3 downward. In the Data sheet, the
Date data is in Column B.

Again, my thanks. You guys save so many of us tons of work (not to mention
the trial and error trying to do it ourselves).

Ron R.

 

[Roger Govier]

Hi Ron

Column() returns a number equal to the column number of the cell where
it is typed. It will thus increase as it is copied across a row.
To begin with, Pete used COLUMN()-2 when the formula was in column C,
which gives a result of 3 - 2 = 1 or the first month of the year. When
dragged across to the next cell, it would 4 -2 =2 and so on.

I made the suggestion of using COLUMN(A1), which would return 1. As it
is copied across, it would alter to COLUMN(B1) and give 2, then
COLUMN(C1) which would give 3.

So using COLUMN() is a way of incrementing a number automatically, and
giving it a direct reference of A1, is just forcing it to start at 1.

It has nothing at all to do with the contents of cell A1 of your sheet.

 

[ronnomad]

Roger,

Thanks for the response. It obviously helps to to understand when you know
the why's and how's, especially when the answer is in english rather than
technospeak. It certainly makes it easier to apply what one learns to other
areas.

Ron

 

[Roger Govier]

Hi Ron

You're very welcome. I 'm glad that Pete and I have helped to increase
your understanding.

 

[Pete_UK]

Ah well, it looks like I missed out on this latest interchange, but
I'm glad you got it working and Roger has explained it well to help
you understand how it works.

One thing that occurred to me is that if you add more data you will
need to extend the named ranges. I think the quickest way is to create
them again as described above - Excel will ask you if you want to
overwrite the existing named range, so just click Yes each time.

Glad to be of help - are you going to try the pivot tables now? <bg>

Pete