Excel

[Guest]

I have a spreadsheet which contains Work Order numbers, craft code, work
order date and estimated hours.There are also other non-relevant cells.
These items are sorted by a controller who decides which priority they are
completed in.
When the job is completed, a clerk enters the actual hours taken to do the
job by craft.This report is exported into Excel and has the same data as the
original except it also has the actual hours by date by craft.

As there are no indexed fields I have concatenated the work order number
with the craft code as this only shows once per work order.

My task - should I choose to accept it - is to try and allocate the
actual hours by work order by craft code by date.There are thousands of work
order but I have shown a couple as an example.

A B C D E 04/01/06 04/02/06
04/03/06 and so on
wo# Craft Est Hrs Date Act Hrs Act Hrs
Act Hrs
123456 10 4 04/01/06 ? ? ?
256354 20 3 04/02/06 ? ? ?

This is always for 5 working days Mon-Fri.

I concatenate the wo# with the craft code as follows:

=(VLOOKUP(CONCATENATE($D4,$E4),$M$4:$P$6,4,0)) Where M46 contains the
range of work orders already concatenated as wo# and Craft Code hence line
one would be 12345610 and so on. Col 4 of the lookup is the actual hours. Now
this works fine for one column but how do I get the date separated so that
the actual hours are shown in the right date?

The data I am pulling from is as follows:

WO# / Craft Date Act Hours
12345610 04/04/06 3
25635420 03/04/06 5

Thanks in advance for any help.

Nigel Forge

 

[gjcase]

How about a pivot table with Date on the left & WO/Craftcode underneat
it, and Sum of hours in the field? Alternatively, if you are doing on
week at a time, WO on the left, Date across the top, and Sum of Hour
in the data field.

---GJ

 

[Guest]

No - won't work as the spreadsheet I am populating is a template required for
all sites across the group but we are new to the group.

Thanks for the idea though.

Nigel

 

[vezerid]

In general this is a problem of "SUMIF with Multiple Criteria" as it is
often asked in the newsgroups. The solution is the SUMPRODUCT function:

=SUMPRODUCT((A1:A100=123456)*(B1:B100=10)*(D1100=--"04/04/06")*(C1:C100))

You can replace the constants with cell references. If so, you only
need to specify the cell for the date, i.e. D1100=K1, where K1 holds
the date.

HTH
Kostis Vezerides

 

[Guest]

Try this
=IF($D3=E$1,VLOOKUP(($A3&$B3)*1,table,3,FALSE),0)

A B C D E F
1 4/3/2006 4/4/2006
2 WO Craft Est Hrs Date Act Hrs Act Hrs
3 123456 10 4 4/3/2006 3 0
4 256354 20 3 4/4/2006 0 5

TABLE
WO# Craft Date Act Hours
12345610 4/3/2006 3
25635420 4/4/2006 5

 

[Guest]

Thanks Kostis - that looks better!

Nigel

 

[Guest]

Hi Kostis

Having used the function it only returns a 1 to show that there is dat there
and not the actual number of the hours which is what I need. Any ideas please?

Thanks again

Nigel

 

[vezerid]

Nigel,

Consider again the formula:
=SUMPRODUCT((A1:A100=123456)*(B1:B100=10)*(D1100=--"04/04/06")*(C1:C100))


As you see it multiplies 4 components. The first three are conditions
and the last one is the data that you want the conditional sum of. I
don't know exactly in which column you have what, I assumed it is in
these columns from your posts. The important thing is that if you omit
the last component, with the amounts, this formula will act as a
counting formula, i.e. it will tell you how many records have 123456 in
WO, 10 in Craft and 04/04/06 in Date.

Each one of the components will produce a True or False. Multiplied,
these turn to 1's or 0's, you will get 1' only if all conditions are
true. These 1's, multiplied with the amounts, will give you a
conditional sum.

There is not much more I can say here, these are formulas that are very
often used in such cases. Just reexamine your ranges and make sure you
include the amount. Write back if you have something more specific.

HTH
Kostis