Formula uncertainty

[Ellie]

I have a workbook with several sheets. On the first sheet are multiple daily
entries. On other sheets are where data comes from sheet 1 into summaries
etc. My problem is that I am trying to get the total number of occurrences
of certain numbers meeting certain fields to go into 1 of 4 categories on the
summary sheet. Is this possible?

e.g.
Sheet 1 - multiple daily entries
Col A = week number
Col B = date
Col C = load no.
Col D = order no.
Col E = customer name/location
Col F = Haulier
Col G = Responsibility code

Summary sheet
e.g.
Haulier Prem Customer Other Total
NW
EA
Total

In Column F of Sheet 1 there are 3 Hauliers, namely EA = WRW and NW = KAM
and HAL. One of these will be entered against each entry per day.

In Column G of Sheet 1 is put a responsibility code. There are a total of
18 non-consecutive numbers split between Haulier, Prem, Customer and Other.
I have created ranges for each of these 4, but so far have been unsuccessful
in creating a formula that works.

Many thanks in advance.

 

[Dav]

You can count the occurances using a sumproduct function, but it is not
clear from your example how you wish to group things

you could do sumproduct((f2:f100="NW")*(g1:g100=1))

This would count the number of occurances of responsibility code1 and
Haulier NW

but with out more imformation for your results required, it is hard to
be more specific

Regards

Dav

 

[Ellie]

Hi Dav

Thanks, but as you have stated I probably haven't made it clear enough.
Hope this example helps.

Sheet 1 - multiple daily entries example
Col B Col F Col G
4 July WRW 2
5 July WRW 2
5 July HAL 5
5 July KAM 7
6 July WRW 12
6 July WRW 9
6 July WRW 7

As you will see from my example of the summary sheet below, I select a
week's worth of data, which in some cases may have no entries for a day
unlike others where there could be many entries and count the number of
occurrences of responsibility codes in a certain category, i.e. haulier etc.
The example I have given above hopefully shows how random the haulier and
responsibility codes are.

Summary Sheet
w/c 3 July Haulier Prem Customer
Other Total
NW 1 0 1
0 2
EA 2 0 3
0 5
Total 3 0 4
0 7

Haulier responsibility codes are 1, 2, 5, 6 & 8
Prem responsibility codes are 4, 10, 11, 13, 16 & 17
Customer responsibility codes are 7, 9, 12, 14 & 15
Other responsibility codes are 3 & 18

The above I have set up as a separate range list, but am not sure if this
was right to do.

The hauliers for the NW are more of a problem, as KAM and HAL accumulate
into the NW area whereas WRW is the only element for EA.

Hope this helps to clarify things a bit more.

Many thanks.

Ellie

 

[Dav]

I have created a solution in the attached zipped spreadsheet. It woul
be harder to explain to you in this forum

I have created 2 columns to recode the Haulier and the responsibilit
data on your sheet1 using a vlookup function

I have then used a sumproduct funtion to create your counts in th
summary table

I have given you 2 ways of doing the summary based on date o
weeknumber

To use date your dates need to be formated as dates on sheet1

Regards

Da

+-------------------------------------------------------------------
|Filename: TestHaulier.zip
|Download: http://www.excelforum.com/attachment.php?postid=4987
+-------------------------------------------------------------------

 

[Ellie]

Dav

Thank you. Unfortunately, my works internet does not permit me to view the
attachment, but will view it on another PC later on today.

Many thanks for your help. It is much appreciated.

Ellie

 

[Ellie]

Hi Dav

Sorry to be bothering you again about this. Thanks for the spreadsheet you
attached, but unfortunately when applying it into the document I work with,
because some days have no entries, due to bank holidays, holidays, or the
rarity of no problems, etc., the result comes back as #N/A.

In the vlookup sections, on the daily entries sheet, I have deleted the
occurrences of #N/A where no entries have occurred against a day, but still
comes back in the summary with #N/A.

Many more thanks for all the help you have provided.

Ellie