sumif statements

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" <vivi@discussions.microsoft.com> wrote in message
news:FB74FFF6-A878-4B01-9D18-3DF66A64995A@microsoft.com...
> 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" <vivi@discussions.microsoft.com> wrote in message
> news:FB74FFF6-A878-4B01-9D18-3DF66A64995A@microsoft.com...
> > 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" <vivi@discussions.microsoft.com> wrote in message
news:FE44F88C-3655-477D-AC42-BF4AC425A8F5@microsoft.com...
> 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" <vivi@discussions.microsoft.com> wrote in message
> > news:FB74FFF6-A878-4B01-9D18-3DF66A64995A@microsoft.com...
> > > 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" <vivi@discussions.microsoft.com> wrote in message
> news:FE44F88C-3655-477D-AC42-BF4AC425A8F5@microsoft.com...
> > 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" <vivi@discussions.microsoft.com> wrote in message
> > > news:FB74FFF6-A878-4B01-9D18-3DF66A64995A@microsoft.com...
> > > > 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?
> > > >
> > >
> > >
> > >
>
>
>

In reference to Bob's formula, are there any text fields in.. A1:A100 etc?

"vivi" <vivi@discussions.microsoft.com> wrote in message
news:9BA81B08-4CA1-40EE-B26E-B1D19E80ACF5@microsoft.com...
> 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" <vivi@discussions.microsoft.com> wrote in message
>> news:FE44F88C-3655-477D-AC42-BF4AC425A8F5@microsoft.com...
>> > 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" <vivi@discussions.microsoft.com> wrote in message
>> > > news:FB74FFF6-A878-4B01-9D18-3DF66A64995A@microsoft.com...
>> > > > 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?
>> > > >
>> > >
>> > >
>> > >
>>
>>
>>

Did you adjust the formula to your data?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"vivi" <vivi@discussions.microsoft.com> wrote in message
news:9BA81B08-4CA1-40EE-B26E-B1D19E80ACF5@microsoft.com...
> 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" <vivi@discussions.microsoft.com> wrote in message
> > news:FE44F88C-3655-477D-AC42-BF4AC425A8F5@microsoft.com...
> > > 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" <vivi@discussions.microsoft.com> wrote in message
> > > > news:FB74FFF6-A878-4B01-9D18-3DF66A64995A@microsoft.com...
> > > > > 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?
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >