# Formula uncertainty

1. ## Formula uncertainty

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 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.

2. 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

3. ## Re: Formula uncertainty

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" wrote:

>
> 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
>
>
> --
> Dav
> ------------------------------------------------------------------------
> Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
>
>

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

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

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

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

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

Regards

Dav

5. ## Re: Formula uncertainty

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

"Dav" wrote:

>
> I have created a solution in the attached zipped spreadsheet. It would
> be harder to explain to you in this forum
>
> I have created 2 columns to recode the Haulier and the responsibility
> data on your sheet1 using a vlookup function
>
> I have then used a sumproduct funtion to create your counts in the
> summary table
>
> I have given you 2 ways of doing the summary based on date or
> weeknumber
>
> To use date your dates need to be formated as dates on sheet1
>
> Regards
>
> Dav
>
>
> +-------------------------------------------------------------------+
> |Filename: TestHaulier.zip |
> +-------------------------------------------------------------------+
>
> --
> Dav
> ------------------------------------------------------------------------
> Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
>
>

6. ## Re: Formula uncertainty

Hi Dav

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.

Ellie

"Dav" wrote:

>
> I have created a solution in the attached zipped spreadsheet. It would
> be harder to explain to you in this forum
>
> I have created 2 columns to recode the Haulier and the responsibility
> data on your sheet1 using a vlookup function
>
> I have then used a sumproduct funtion to create your counts in the
> summary table
>
> I have given you 2 ways of doing the summary based on date or
> weeknumber
>
> To use date your dates need to be formated as dates on sheet1
>
> Regards
>
> Dav
>
>
> +-------------------------------------------------------------------+
> |Filename: TestHaulier.zip |
> +-------------------------------------------------------------------+
>
> --
> Dav
> ------------------------------------------------------------------------
> Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
>
>

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1