Hi there

I am trying to create a report in order to calculate spends per customer
which consists of two work sheets €“

The first worksheet has all the sales data as well as the sites and customer
mixed in the same worksheet

The second sheet need to summarize the data and need to lookup the
merchandised category in column A, customer type in column B and the sites in
column C all from sheet 1and enter the total value in column D in sheet 2. I
know I need to use a conditional sum statement for this but not so sure how
to do it, can any body help me?

=SUMPRODUCT(--(Sheet1!A1:A100=A1),--(Sheet1!B1:B100=B1),--(Sheet1!C:C00=C1),
Sheet1!D1:D100)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"vivi" wrote in message
...
Hi there

I am trying to create a report in order to calculate spends per customer
which consists of two work sheets -

The first worksheet has all the sales data as well as the sites and
customer
mixed in the same worksheet

The second sheet need to summarize the data and need to lookup the
merchandised category in column A, customer type in column B and the sites
in
column C all from sheet 1and enter the total value in column D in sheet 2.
I
know I need to use a conditional sum statement for this but not so sure
how
to do it, can any body help me?

thanks for your help...i've tested he formula out but it has #NUM! instead
the sumup value which should be 96?.....any ideas??thanks

"Bob Phillips" wrote:

=SUMPRODUCT(--(Sheet1!A1:A100=A1),--(Sheet1!B1:B100=B1),--(Sheet1!C:C00=C1),
Sheet1!D1:D100)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"vivi" wrote in message
...
Hi there

I am trying to create a report in order to calculate spends per customer
which consists of two work sheets -

The first worksheet has all the sales data as well as the sites and
customer
mixed in the same worksheet

The second sheet need to summarize the data and need to lookup the
merchandised category in column A, customer type in column B and the sites
in
column C all from sheet 1and enter the total value in column D in sheet 2.
I
know I need to use a conditional sum statement for this but not so sure
how
to do it, can any body help me?

Sorry, my mistake, try this

=SUMPRODUCT(--(Sheet1!A1:A100=A1),--(Sheet1!B1:B100=B1),
--(Sheet1!C1:C100=C1),Sheet1!D1:D100)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"vivi" wrote in message
...
thanks for your help...i've tested he formula out but it has #NUM! instead
the sumup value which should be 96?.....any ideas??thanks

"Bob Phillips" wrote:


=SUMPRODUCT(--(Sheet1!A1:A100=A1),--(Sheet1!B1:B100=B1),--(Sheet1!C:C00=C1),
Sheet1!D1:D100)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"vivi" wrote in message
...
Hi there

I am trying to create a report in order to calculate spends per
customer
which consists of two work sheets -

The first worksheet has all the sales data as well as the sites and
customer
mixed in the same worksheet

The second sheet need to summarize the data and need to lookup the
merchandised category in column A, customer type in column B and the
sites
in
column C all from sheet 1and enter the total value in column D in
sheet 2.
I
know I need to use a conditional sum statement for this but not so
sure
how
to do it, can any body help me?

Hi Bob..I've tried that too....still not working...isn't it sum product is
used for mutliplication? my column A B and C are all text...will that change
anything?

Sorry about this prolonged issue..and thanks for your help and patients...

Vivi

"Bob Phillips" wrote:

Sorry, my mistake, try this

=SUMPRODUCT(--(Sheet1!A1:A100=A1),--(Sheet1!B1:B100=B1),
--(Sheet1!C1:C100=C1),Sheet1!D1:D100)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"vivi" wrote in message
...
thanks for your help...i've tested he formula out but it has #NUM! instead
the sumup value which should be 96?.....any ideas??thanks

"Bob Phillips" wrote:


=SUMPRODUCT(--(Sheet1!A1:A100=A1),--(Sheet1!B1:B100=B1),--(Sheet1!C:C00=C1),
Sheet1!D1:D100)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"vivi" wrote in message
...
Hi there

I am trying to create a report in order to calculate spends per
customer
which consists of two work sheets -

The first worksheet has all the sales data as well as the sites and
customer
mixed in the same worksheet

The second sheet need to summarize the data and need to lookup the
merchandised category in column A, customer type in column B and the
sites
in
column C all from sheet 1and enter the total value in column D in
sheet 2.
I
know I need to use a conditional sum statement for this but not so
sure
how
to do it, can any body help me?