I have a spreadsheet with multiple worksheets.

The worksheets relevant to my SUMPRODUCT problem are as follows:-
Daily Report 2006 - this holds all the core data
NW Customer Summary - this holds a weekly summary of issues occurring during
a specific week, along with whether they are Haul, Prem, Cust or Other.
EA Customer Summary - this is the same as the NW.
Both the NW and EA Customer Summaries information comes from the Daily
Report, where a vlookup has been established indicating whether it is EA or
NW, along with whether it is Haul, Prem, Cust or Other.

I have put together the following formula, but have so far been
unsuccessful, so am unsure as to whether I have put too much information in,
not looking at areas in the right order etc. Could anyone help me with this,
please.

I have not put this into the live document, so any reference of Sheet2 is
referring to my example of EA Customer Summary to see if I could get it to
work.

=SUMPRODUCT(--('Daily Report 2006'!$B$2:$B$336=Sheet2!$A$3),--('Daily
Report 2006'!$B$2:$B$336<Sheet2!$A$3+7)--'Daily Report
2006'!$L$2:$L$336=$B$1)*('Daily Report 2006'!$M$2:$M$336=Sheet2!$D$2)*('Daily
Report 2006'!$F$2:$F$336=Sheet2!$B$3)

Sheet2 Cell A3 refers to the w/c date, for which I only require a week's
worth of data in each element. It is looking at B2:B336 on Sheet2 for each
date instance.

Daily Report 2006 L2:L336 refers to whether the occurrence was either EA or
NW, also as per a separate vlookup. Sheet2 B1 is in the hidden column/cell
to dictate whether the summary is for the NW or EA.

Daily Report 2006 M2:M336 refers to Haul, Prem, Cust or Other from vlookup
option in the report with D2 in Sheet2 referring to the Haul, Prem, Cust or
Other heading in that week's section.

Also in the hidden column on Sheet2 I have put the short name customers,
i.e. Asda, JS, Morr, Tesco and Other. Each customer has a row with each
customer having an entry for Haul, Prem, Cust or Other and can some times
have no entries in a week and others a variety. Column F in the Daily Report
2006 reflects the shortened version of the customer name/location for which I
am trying to get the information to come together.

Example of document I am trying to establish this formula into is as follows:-
EA
04-Sep Haul Prem Cust Other
Asda
JS
Tesco
Morr
Other
Total

Many thanks in advance.

If the formula is exactly as posted, there is a problem, so try this

=SUMPRODUCT(--('Daily Report 2006'!$B$2:$B$336=Sheet2!$A$3),
--('Daily Report 2006'!$B$2:$B$336<Sheet2!$A$3+7),
--('Daily Report 2006'!$L$2:$L$336=$B$1),
--('Daily Report 2006'!$M$2:$M$336=Sheet2!$D$2),
--('Daily Report 2006'!$F$2:$F$336=Sheet2!$B$3))


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Ellie" wrote in message
...
I have a spreadsheet with multiple worksheets.

The worksheets relevant to my SUMPRODUCT problem are as follows:-
Daily Report 2006 - this holds all the core data
NW Customer Summary - this holds a weekly summary of issues occurring
during
a specific week, along with whether they are Haul, Prem, Cust or Other.
EA Customer Summary - this is the same as the NW.
Both the NW and EA Customer Summaries information comes from the Daily
Report, where a vlookup has been established indicating whether it is EA
or
NW, along with whether it is Haul, Prem, Cust or Other.

I have put together the following formula, but have so far been
unsuccessful, so am unsure as to whether I have put too much information
in,
not looking at areas in the right order etc. Could anyone help me with
this,
please.

I have not put this into the live document, so any reference of Sheet2 is
referring to my example of EA Customer Summary to see if I could get it to
work.

=SUMPRODUCT(--('Daily Report 2006'!$B$2:$B$336=Sheet2!$A$3),--('Daily
Report 2006'!$B$2:$B$336<Sheet2!$A$3+7)--'Daily Report
2006'!$L$2:$L$336=$B$1)*('Daily Report
2006'!$M$2:$M$336=Sheet2!$D$2)*('Daily
Report 2006'!$F$2:$F$336=Sheet2!$B$3)

Sheet2 Cell A3 refers to the w/c date, for which I only require a week's
worth of data in each element. It is looking at B2:B336 on Sheet2 for
each
date instance.

Daily Report 2006 L2:L336 refers to whether the occurrence was either EA
or
NW, also as per a separate vlookup. Sheet2 B1 is in the hidden
column/cell
to dictate whether the summary is for the NW or EA.

Daily Report 2006 M2:M336 refers to Haul, Prem, Cust or Other from vlookup
option in the report with D2 in Sheet2 referring to the Haul, Prem, Cust
or
Other heading in that week's section.

Also in the hidden column on Sheet2 I have put the short name customers,
i.e. Asda, JS, Morr, Tesco and Other. Each customer has a row with each
customer having an entry for Haul, Prem, Cust or Other and can some times
have no entries in a week and others a variety. Column F in the Daily
Report
2006 reflects the shortened version of the customer name/location for
which I
am trying to get the information to come together.

Example of document I am trying to establish this formula into is as
follows:-
EA
04-Sep Haul Prem Cust Other
Asda
JS
Tesco
Morr
Other
Total

Many thanks in advance.

Hi Bob

Thanks for your reply. Having made the changes as per your suggestion,
unfortunately I am still getting the result of #VALUE!, which is why I wasn't
sure if I had entered anything wrong or was expecting too much from the
formula.

"Bob Phillips" wrote:

If the formula is exactly as posted, there is a problem, so try this

=SUMPRODUCT(--('Daily Report 2006'!$B$2:$B$336=Sheet2!$A$3),
--('Daily Report 2006'!$B$2:$B$336<Sheet2!$A$3+7),
--('Daily Report 2006'!$L$2:$L$336=$B$1),
--('Daily Report 2006'!$M$2:$M$336=Sheet2!$D$2),
--('Daily Report 2006'!$F$2:$F$336=Sheet2!$B$3))


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Ellie" wrote in message
...
I have a spreadsheet with multiple worksheets.

The worksheets relevant to my SUMPRODUCT problem are as follows:-
Daily Report 2006 - this holds all the core data
NW Customer Summary - this holds a weekly summary of issues occurring
during
a specific week, along with whether they are Haul, Prem, Cust or Other.
EA Customer Summary - this is the same as the NW.
Both the NW and EA Customer Summaries information comes from the Daily
Report, where a vlookup has been established indicating whether it is EA
or
NW, along with whether it is Haul, Prem, Cust or Other.

I have put together the following formula, but have so far been
unsuccessful, so am unsure as to whether I have put too much information
in,
not looking at areas in the right order etc. Could anyone help me with
this,
please.

I have not put this into the live document, so any reference of Sheet2 is
referring to my example of EA Customer Summary to see if I could get it to
work.

=SUMPRODUCT(--('Daily Report 2006'!$B$2:$B$336=Sheet2!$A$3),--('Daily
Report 2006'!$B$2:$B$336<Sheet2!$A$3+7)--'Daily Report
2006'!$L$2:$L$336=$B$1)*('Daily Report
2006'!$M$2:$M$336=Sheet2!$D$2)*('Daily
Report 2006'!$F$2:$F$336=Sheet2!$B$3)

Sheet2 Cell A3 refers to the w/c date, for which I only require a week's
worth of data in each element. It is looking at B2:B336 on Sheet2 for
each
date instance.

Daily Report 2006 L2:L336 refers to whether the occurrence was either EA
or
NW, also as per a separate vlookup. Sheet2 B1 is in the hidden
column/cell
to dictate whether the summary is for the NW or EA.

Daily Report 2006 M2:M336 refers to Haul, Prem, Cust or Other from vlookup
option in the report with D2 in Sheet2 referring to the Haul, Prem, Cust
or
Other heading in that week's section.

Also in the hidden column on Sheet2 I have put the short name customers,
i.e. Asda, JS, Morr, Tesco and Other. Each customer has a row with each
customer having an entry for Haul, Prem, Cust or Other and can some times
have no entries in a week and others a variety. Column F in the Daily
Report
2006 reflects the shortened version of the customer name/location for
which I
am trying to get the information to come together.

Example of document I am trying to establish this formula into is as
follows:-
EA
04-Sep Haul Prem Cust Other
Asda
JS
Tesco
Morr
Other
Total

Many thanks in advance.

The formula is no big deal in itself, should work. Can you post a workbook
to one of the temporary web hosts to look at? It would take too long to work
through your data.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Ellie" wrote in message
...
I have a spreadsheet with multiple worksheets.

The worksheets relevant to my SUMPRODUCT problem are as follows:-
Daily Report 2006 - this holds all the core data
NW Customer Summary - this holds a weekly summary of issues occurring
during
a specific week, along with whether they are Haul, Prem, Cust or Other.
EA Customer Summary - this is the same as the NW.
Both the NW and EA Customer Summaries information comes from the Daily
Report, where a vlookup has been established indicating whether it is EA
or
NW, along with whether it is Haul, Prem, Cust or Other.

I have put together the following formula, but have so far been
unsuccessful, so am unsure as to whether I have put too much information
in,
not looking at areas in the right order etc. Could anyone help me with
this,
please.

I have not put this into the live document, so any reference of Sheet2 is
referring to my example of EA Customer Summary to see if I could get it to
work.

=SUMPRODUCT(--('Daily Report 2006'!$B$2:$B$336=Sheet2!$A$3),--('Daily
Report 2006'!$B$2:$B$336<Sheet2!$A$3+7)--'Daily Report
2006'!$L$2:$L$336=$B$1)*('Daily Report
2006'!$M$2:$M$336=Sheet2!$D$2)*('Daily
Report 2006'!$F$2:$F$336=Sheet2!$B$3)

Sheet2 Cell A3 refers to the w/c date, for which I only require a week's
worth of data in each element. It is looking at B2:B336 on Sheet2 for
each
date instance.

Daily Report 2006 L2:L336 refers to whether the occurrence was either EA
or
NW, also as per a separate vlookup. Sheet2 B1 is in the hidden
column/cell
to dictate whether the summary is for the NW or EA.

Daily Report 2006 M2:M336 refers to Haul, Prem, Cust or Other from vlookup
option in the report with D2 in Sheet2 referring to the Haul, Prem, Cust
or
Other heading in that week's section.

Also in the hidden column on Sheet2 I have put the short name customers,
i.e. Asda, JS, Morr, Tesco and Other. Each customer has a row with each
customer having an entry for Haul, Prem, Cust or Other and can some times
have no entries in a week and others a variety. Column F in the Daily
Report
2006 reflects the shortened version of the customer name/location for
which I
am trying to get the information to come together.

Example of document I am trying to establish this formula into is as
follows:-
EA
04-Sep Haul Prem Cust Other
Asda
JS
Tesco
Morr
Other
Total

Many thanks in advance.

Hi Bob

Unfortunately my works servers restrict access to some areas and have been
unsuccessful in using temporary web hosts. Would it be possible for me to
e-mail you direct the document?

Ellie

"Bob Phillips" wrote:

The formula is no big deal in itself, should work. Can you post a workbook
to one of the temporary web hosts to look at? It would take too long to work
through your data.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Ellie" wrote in message
...
I have a spreadsheet with multiple worksheets.

The worksheets relevant to my SUMPRODUCT problem are as follows:-
Daily Report 2006 - this holds all the core data
NW Customer Summary - this holds a weekly summary of issues occurring
during
a specific week, along with whether they are Haul, Prem, Cust or Other.
EA Customer Summary - this is the same as the NW.
Both the NW and EA Customer Summaries information comes from the Daily
Report, where a vlookup has been established indicating whether it is EA
or
NW, along with whether it is Haul, Prem, Cust or Other.

I have put together the following formula, but have so far been
unsuccessful, so am unsure as to whether I have put too much information
in,
not looking at areas in the right order etc. Could anyone help me with
this,
please.

I have not put this into the live document, so any reference of Sheet2 is
referring to my example of EA Customer Summary to see if I could get it to
work.

=SUMPRODUCT(--('Daily Report 2006'!$B$2:$B$336=Sheet2!$A$3),--('Daily
Report 2006'!$B$2:$B$336<Sheet2!$A$3+7)--'Daily Report
2006'!$L$2:$L$336=$B$1)*('Daily Report
2006'!$M$2:$M$336=Sheet2!$D$2)*('Daily
Report 2006'!$F$2:$F$336=Sheet2!$B$3)

Sheet2 Cell A3 refers to the w/c date, for which I only require a week's
worth of data in each element. It is looking at B2:B336 on Sheet2 for
each
date instance.

Daily Report 2006 L2:L336 refers to whether the occurrence was either EA
or
NW, also as per a separate vlookup. Sheet2 B1 is in the hidden
column/cell
to dictate whether the summary is for the NW or EA.

Daily Report 2006 M2:M336 refers to Haul, Prem, Cust or Other from vlookup
option in the report with D2 in Sheet2 referring to the Haul, Prem, Cust
or
Other heading in that week's section.

Also in the hidden column on Sheet2 I have put the short name customers,
i.e. Asda, JS, Morr, Tesco and Other. Each customer has a row with each
customer having an entry for Haul, Prem, Cust or Other and can some times
have no entries in a week and others a variety. Column F in the Daily
Report
2006 reflects the shortened version of the customer name/location for
which I
am trying to get the information to come together.

Example of document I am trying to establish this formula into is as
follows:-
EA
04-Sep Haul Prem Cust Other
Asda
JS
Tesco
Morr
Other
Total

Many thanks in advance.

sure, bob dot ngs at googlemail dot com

do the obvious

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Ellie" wrote in message
...
Hi Bob

Unfortunately my works servers restrict access to some areas and have been
unsuccessful in using temporary web hosts. Would it be possible for me to
e-mail you direct the document?

Ellie

"Bob Phillips" wrote:

The formula is no big deal in itself, should work. Can you post a
workbook
to one of the temporary web hosts to look at? It would take too long to
work
through your data.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Ellie" wrote in message
...
I have a spreadsheet with multiple worksheets.

The worksheets relevant to my SUMPRODUCT problem are as follows:-
Daily Report 2006 - this holds all the core data
NW Customer Summary - this holds a weekly summary of issues occurring
during
a specific week, along with whether they are Haul, Prem, Cust or
Other.
EA Customer Summary - this is the same as the NW.
Both the NW and EA Customer Summaries information comes from the Daily
Report, where a vlookup has been established indicating whether it is
EA
or
NW, along with whether it is Haul, Prem, Cust or Other.

I have put together the following formula, but have so far been
unsuccessful, so am unsure as to whether I have put too much
information
in,
not looking at areas in the right order etc. Could anyone help me
with
this,
please.

I have not put this into the live document, so any reference of Sheet2
is
referring to my example of EA Customer Summary to see if I could get
it to
work.

=SUMPRODUCT(--('Daily Report 2006'!$B$2:$B$336=Sheet2!$A$3),--('Daily
Report 2006'!$B$2:$B$336<Sheet2!$A$3+7)--'Daily Report
2006'!$L$2:$L$336=$B$1)*('Daily Report
2006'!$M$2:$M$336=Sheet2!$D$2)*('Daily
Report 2006'!$F$2:$F$336=Sheet2!$B$3)

Sheet2 Cell A3 refers to the w/c date, for which I only require a
week's
worth of data in each element. It is looking at B2:B336 on Sheet2 for
each
date instance.

Daily Report 2006 L2:L336 refers to whether the occurrence was either
EA
or
NW, also as per a separate vlookup. Sheet2 B1 is in the hidden
column/cell
to dictate whether the summary is for the NW or EA.

Daily Report 2006 M2:M336 refers to Haul, Prem, Cust or Other from
vlookup
option in the report with D2 in Sheet2 referring to the Haul, Prem,
Cust
or
Other heading in that week's section.

Also in the hidden column on Sheet2 I have put the short name
customers,
i.e. Asda, JS, Morr, Tesco and Other. Each customer has a row with
each
customer having an entry for Haul, Prem, Cust or Other and can some
times
have no entries in a week and others a variety. Column F in the Daily
Report
2006 reflects the shortened version of the customer name/location for
which I
am trying to get the information to come together.

Example of document I am trying to establish this formula into is as
follows:-
EA
04-Sep Haul Prem Cust Other
Asda
JS
Tesco
Morr
Other
Total

Many thanks in advance.

Ellie,

Your formula is this

=SUMPRODUCT(--('Daily Report 2006'!$B$2:$B$336=$A$3),
--('Daily Report 2006'!$B$2:$B$336<Sheet2!$A$3+7)
--'Daily Report 2006'!$L$2:$L$336=$B$1)
--('Daily Report 2006'!$M$2:$M$336=Sheet2!$D$2)
--('Daily Report 2006'!$F$2:$F$336=Sheet2!$B$3)

each condition still needs comma separation, so if you used what I showed
you last time,

=SUMPRODUCT(--('Daily Report 2006'!$B$2:$B$336=Sheet2!$A$3),
--('Daily Report 2006'!$B$2:$B$336<Sheet2!$A$3+7),
--('Daily Report 2006'!$L$2:$L$336=$B$1),
--('Daily Report 2006'!$M$2:$M$336=Sheet2!$D$2),
--('Daily Report 2006'!$F$2:$F$336=Sheet2!$B$3))

that would have worked. Note the differences.

You can also remove the references to this sheet

=SUMPRODUCT(--('Daily Report 2006'!$B$2:$B$336=$A$3),
--('Daily Report 2006'!$B$2:$B$336<$A$3+7),
--('Daily Report 2006'!$L$2:$L$336=$B$1),
--('Daily Report 2006'!$M$2:$M$336=$D$2),
--('Daily Report 2006'!$F$2:$F$336=$B$3))

and you should reduce the absolute-absolute references to facilitate easy
copying

=SUMPRODUCT(--('Daily Report 2006'!$B$2:$B$336=$A$3),
--('Daily Report 2006'!$B$2:$B$336<$A$3+7),
--('Daily Report 2006'!$L$2:$L$336=$B$1),
--('Daily Report 2006'!$M$2:$M$336=D$2),
--('Daily Report 2006'!$F$2:$F$336=$B3))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Bob Phillips" wrote in message
...
sure, bob dot ngs at googlemail dot com

do the obvious

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Ellie" wrote in message
...
Hi Bob

Unfortunately my works servers restrict access to some areas and have
been
unsuccessful in using temporary web hosts. Would it be possible for me
to
e-mail you direct the document?

Ellie

"Bob Phillips" wrote:

The formula is no big deal in itself, should work. Can you post a
workbook
to one of the temporary web hosts to look at? It would take too long
to
work
through your data.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Ellie" wrote in message
...
I have a spreadsheet with multiple worksheets.

The worksheets relevant to my SUMPRODUCT problem are as follows:-
Daily Report 2006 - this holds all the core data
NW Customer Summary - this holds a weekly summary of issues
occurring
during
a specific week, along with whether they are Haul, Prem, Cust or
Other.
EA Customer Summary - this is the same as the NW.
Both the NW and EA Customer Summaries information comes from the
Daily
Report, where a vlookup has been established indicating whether it
is
EA
or
NW, along with whether it is Haul, Prem, Cust or Other.

I have put together the following formula, but have so far been
unsuccessful, so am unsure as to whether I have put too much
information
in,
not looking at areas in the right order etc. Could anyone help me
with
this,
please.

I have not put this into the live document, so any reference of
Sheet2
is
referring to my example of EA Customer Summary to see if I could get
it to
work.

=SUMPRODUCT(--('Daily Report
2006'!$B$2:$B$336=Sheet2!$A$3),--('Daily
Report 2006'!$B$2:$B$336<Sheet2!$A$3+7)--'Daily Report
2006'!$L$2:$L$336=$B$1)*('Daily Report
2006'!$M$2:$M$336=Sheet2!$D$2)*('Daily
Report 2006'!$F$2:$F$336=Sheet2!$B$3)

Sheet2 Cell A3 refers to the w/c date, for which I only require a
week's
worth of data in each element. It is looking at B2:B336 on Sheet2
for
each
date instance.

Daily Report 2006 L2:L336 refers to whether the occurrence was
either
EA
or
NW, also as per a separate vlookup. Sheet2 B1 is in the hidden
column/cell
to dictate whether the summary is for the NW or EA.

Daily Report 2006 M2:M336 refers to Haul, Prem, Cust or Other from
vlookup
option in the report with D2 in Sheet2 referring to the Haul, Prem,
Cust
or
Other heading in that week's section.

Also in the hidden column on Sheet2 I have put the short name
customers,
i.e. Asda, JS, Morr, Tesco and Other. Each customer has a row with
each
customer having an entry for Haul, Prem, Cust or Other and can some
times
have no entries in a week and others a variety. Column F in the
Daily
Report
2006 reflects the shortened version of the customer name/location
for
which I
am trying to get the information to come together.

Example of document I am trying to establish this formula into is as
follows:-
EA
04-Sep Haul Prem Cust Other
Asda
JS
Tesco
Morr
Other
Total

Many thanks in advance.

Many thanks, Bob.

Apologies for missing the commas, I must have rushed through it when reading
it, so apologies again and many thanks for your time and patience.

Ellie

"Bob Phillips" wrote:

Ellie,

Your formula is this

=SUMPRODUCT(--('Daily Report 2006'!$B$2:$B$336=$A$3),
--('Daily Report 2006'!$B$2:$B$336<Sheet2!$A$3+7)
--'Daily Report 2006'!$L$2:$L$336=$B$1)
--('Daily Report 2006'!$M$2:$M$336=Sheet2!$D$2)
--('Daily Report 2006'!$F$2:$F$336=Sheet2!$B$3)

each condition still needs comma separation, so if you used what I showed
you last time,

=SUMPRODUCT(--('Daily Report 2006'!$B$2:$B$336=Sheet2!$A$3),
--('Daily Report 2006'!$B$2:$B$336<Sheet2!$A$3+7),
--('Daily Report 2006'!$L$2:$L$336=$B$1),
--('Daily Report 2006'!$M$2:$M$336=Sheet2!$D$2),
--('Daily Report 2006'!$F$2:$F$336=Sheet2!$B$3))

that would have worked. Note the differences.

You can also remove the references to this sheet

=SUMPRODUCT(--('Daily Report 2006'!$B$2:$B$336=$A$3),
--('Daily Report 2006'!$B$2:$B$336<$A$3+7),
--('Daily Report 2006'!$L$2:$L$336=$B$1),
--('Daily Report 2006'!$M$2:$M$336=$D$2),
--('Daily Report 2006'!$F$2:$F$336=$B$3))

and you should reduce the absolute-absolute references to facilitate easy
copying

=SUMPRODUCT(--('Daily Report 2006'!$B$2:$B$336=$A$3),
--('Daily Report 2006'!$B$2:$B$336<$A$3+7),
--('Daily Report 2006'!$L$2:$L$336=$B$1),
--('Daily Report 2006'!$M$2:$M$336=D$2),
--('Daily Report 2006'!$F$2:$F$336=$B3))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Bob Phillips" wrote in message
...
sure, bob dot ngs at googlemail dot com

do the obvious

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Ellie" wrote in message
...
Hi Bob

Unfortunately my works servers restrict access to some areas and have
been
unsuccessful in using temporary web hosts. Would it be possible for me
to
e-mail you direct the document?

Ellie

"Bob Phillips" wrote:

The formula is no big deal in itself, should work. Can you post a
workbook
to one of the temporary web hosts to look at? It would take too long
to
work
through your data.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Ellie" wrote in message
...
I have a spreadsheet with multiple worksheets.

The worksheets relevant to my SUMPRODUCT problem are as follows:-
Daily Report 2006 - this holds all the core data
NW Customer Summary - this holds a weekly summary of issues
occurring
during
a specific week, along with whether they are Haul, Prem, Cust or
Other.
EA Customer Summary - this is the same as the NW.
Both the NW and EA Customer Summaries information comes from the
Daily
Report, where a vlookup has been established indicating whether it
is
EA
or
NW, along with whether it is Haul, Prem, Cust or Other.

I have put together the following formula, but have so far been
unsuccessful, so am unsure as to whether I have put too much
information
in,
not looking at areas in the right order etc. Could anyone help me
with
this,
please.

I have not put this into the live document, so any reference of
Sheet2
is
referring to my example of EA Customer Summary to see if I could get
it to
work.

=SUMPRODUCT(--('Daily Report
2006'!$B$2:$B$336=Sheet2!$A$3),--('Daily
Report 2006'!$B$2:$B$336<Sheet2!$A$3+7)--'Daily Report
2006'!$L$2:$L$336=$B$1)*('Daily Report
2006'!$M$2:$M$336=Sheet2!$D$2)*('Daily
Report 2006'!$F$2:$F$336=Sheet2!$B$3)

Sheet2 Cell A3 refers to the w/c date, for which I only require a
week's
worth of data in each element. It is looking at B2:B336 on Sheet2
for
each
date instance.

Daily Report 2006 L2:L336 refers to whether the occurrence was
either
EA
or
NW, also as per a separate vlookup. Sheet2 B1 is in the hidden
column/cell
to dictate whether the summary is for the NW or EA.

Daily Report 2006 M2:M336 refers to Haul, Prem, Cust or Other from
vlookup
option in the report with D2 in Sheet2 referring to the Haul, Prem,
Cust
or
Other heading in that week's section.

Also in the hidden column on Sheet2 I have put the short name
customers,
i.e. Asda, JS, Morr, Tesco and Other. Each customer has a row with
each
customer having an entry for Haul, Prem, Cust or Other and can some
times
have no entries in a week and others a variety. Column F in the
Daily
Report
2006 reflects the shortened version of the customer name/location
for
which I
am trying to get the information to come together.

Example of document I am trying to establish this formula into is as
follows:-
EA
04-Sep Haul Prem Cust Other
Asda
JS
Tesco
Morr
Other
Total

Many thanks in advance.

No problems, glad you are sorted.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Ellie" wrote in message
...
Many thanks, Bob.

Apologies for missing the commas, I must have rushed through it when
reading
it, so apologies again and many thanks for your time and patience.

Ellie

"Bob Phillips" wrote:

Ellie,

Your formula is this

=SUMPRODUCT(--('Daily Report 2006'!$B$2:$B$336=$A$3),
--('Daily Report 2006'!$B$2:$B$336<Sheet2!$A$3+7)
--'Daily Report 2006'!$L$2:$L$336=$B$1)
--('Daily Report 2006'!$M$2:$M$336=Sheet2!$D$2)
--('Daily Report 2006'!$F$2:$F$336=Sheet2!$B$3)

each condition still needs comma separation, so if you used what I
showed
you last time,

=SUMPRODUCT(--('Daily Report 2006'!$B$2:$B$336=Sheet2!$A$3),
--('Daily Report 2006'!$B$2:$B$336<Sheet2!$A$3+7),
--('Daily Report 2006'!$L$2:$L$336=$B$1),
--('Daily Report 2006'!$M$2:$M$336=Sheet2!$D$2),
--('Daily Report 2006'!$F$2:$F$336=Sheet2!$B$3))

that would have worked. Note the differences.

You can also remove the references to this sheet

=SUMPRODUCT(--('Daily Report 2006'!$B$2:$B$336=$A$3),
--('Daily Report 2006'!$B$2:$B$336<$A$3+7),
--('Daily Report 2006'!$L$2:$L$336=$B$1),
--('Daily Report 2006'!$M$2:$M$336=$D$2),
--('Daily Report 2006'!$F$2:$F$336=$B$3))

and you should reduce the absolute-absolute references to facilitate
easy
copying

=SUMPRODUCT(--('Daily Report 2006'!$B$2:$B$336=$A$3),
--('Daily Report 2006'!$B$2:$B$336<$A$3+7),
--('Daily Report 2006'!$L$2:$L$336=$B$1),
--('Daily Report 2006'!$M$2:$M$336=D$2),
--('Daily Report 2006'!$F$2:$F$336=$B3))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Bob Phillips" wrote in message
...
sure, bob dot ngs at googlemail dot com

do the obvious

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Ellie" wrote in message
...
Hi Bob

Unfortunately my works servers restrict access to some areas and
have
been
unsuccessful in using temporary web hosts. Would it be possible for
me
to
e-mail you direct the document?

Ellie

"Bob Phillips" wrote:

The formula is no big deal in itself, should work. Can you post a
workbook
to one of the temporary web hosts to look at? It would take too
long
to
work
through your data.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Ellie" wrote in message
...
I have a spreadsheet with multiple worksheets.

The worksheets relevant to my SUMPRODUCT problem are as
follows:-
Daily Report 2006 - this holds all the core data
NW Customer Summary - this holds a weekly summary of issues
occurring
during
a specific week, along with whether they are Haul, Prem, Cust or
Other.
EA Customer Summary - this is the same as the NW.
Both the NW and EA Customer Summaries information comes from the
Daily
Report, where a vlookup has been established indicating whether
it
is
EA
or
NW, along with whether it is Haul, Prem, Cust or Other.

I have put together the following formula, but have so far been
unsuccessful, so am unsure as to whether I have put too much
information
in,
not looking at areas in the right order etc. Could anyone help
me
with
this,
please.

I have not put this into the live document, so any reference of
Sheet2
is
referring to my example of EA Customer Summary to see if I could
get
it to
work.

=SUMPRODUCT(--('Daily Report
2006'!$B$2:$B$336=Sheet2!$A$3),--('Daily
Report 2006'!$B$2:$B$336<Sheet2!$A$3+7)--'Daily Report
2006'!$L$2:$L$336=$B$1)*('Daily Report
2006'!$M$2:$M$336=Sheet2!$D$2)*('Daily
Report 2006'!$F$2:$F$336=Sheet2!$B$3)

Sheet2 Cell A3 refers to the w/c date, for which I only require
a
week's
worth of data in each element. It is looking at B2:B336 on
Sheet2
for
each
date instance.

Daily Report 2006 L2:L336 refers to whether the occurrence was
either
EA
or
NW, also as per a separate vlookup. Sheet2 B1 is in the hidden
column/cell
to dictate whether the summary is for the NW or EA.

Daily Report 2006 M2:M336 refers to Haul, Prem, Cust or Other
from
vlookup
option in the report with D2 in Sheet2 referring to the Haul,
Prem,
Cust
or
Other heading in that week's section.

Also in the hidden column on Sheet2 I have put the short name
customers,
i.e. Asda, JS, Morr, Tesco and Other. Each customer has a row
with
each
customer having an entry for Haul, Prem, Cust or Other and can
some
times
have no entries in a week and others a variety. Column F in the
Daily
Report
2006 reflects the shortened version of the customer
name/location
for
which I
am trying to get the information to come together.

Example of document I am trying to establish this formula into
is as
follows:-
EA
04-Sep Haul Prem Cust Other
Asda
JS
Tesco
Morr
Other
Total

Many thanks in advance.