SUMIF INDIRECT

Hi

Can anyone tell me how to sum a range of numbers in column D, provided that
adjacent cells in row A contain "Apples" and row C contain "Oranges".

Thanks
Alec

=SUMPRODUCT(--(A2:A200="Apples"),--(C2:C200="Oranges"),D2:D200)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Alectrical" <Alectrical@discussions.microsoft.com> wrote in message
news:DDB54BE4-BC29-4A54-B990-29836E0FD0F3@microsoft.com...
> Hi
>
> Can anyone tell me how to sum a range of numbers in column D, provided
that
> adjacent cells in row A contain "Apples" and row C contain "Oranges".
>
> Thanks
> Alec

Thanks Bob

"Bob Phillips" wrote:

> =SUMPRODUCT(--(A2:A200="Apples"),--(C2:C200="Oranges"),D2:D200)
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Alectrical" <Alectrical@discussions.microsoft.com> wrote in message
> news:DDB54BE4-BC29-4A54-B990-29836E0FD0F3@microsoft.com...
> > Hi
> >
> > Can anyone tell me how to sum a range of numbers in column D, provided
> that
> > adjacent cells in row A contain "Apples" and row C contain "Oranges".
> >
> > Thanks
> > Alec
>
>
>

Hi

Try something like this:

=SUMPRODUCT((A2:A1000="Apples")*(C2:C1000="Oranges")*(D2:D1000))

When using Sumproduct, make sure that the ranges are all the same size and
are not full columns.

Hope this helps.
Andy.

"Alectrical" <Alectrical@discussions.microsoft.com> wrote in message
news:DDB54BE4-BC29-4A54-B990-29836E0FD0F3@microsoft.com...
> Hi
>
> Can anyone tell me how to sum a range of numbers in column D, provided
> that
> adjacent cells in row A contain "Apples" and row C contain "Oranges".
>
> Thanks
> Alec

Thanks Andy, your function works on a new sheet, but when I type the
following function into an existing sheet I get the #VALUE message. Any ideas.

=SUMPRODUCT((A13:A6000="IO")*(F13:F6000="2PLC")*(H13:H6000))

"Andy" wrote:

> Hi
>
> Try something like this:
>
> =SUMPRODUCT((A2:A1000="Apples")*(C2:C1000="Oranges")*(D2:D1000))
>
> When using Sumproduct, make sure that the ranges are all the same size and
> are not full columns.
>
> Hope this helps.
> Andy.
>
> "Alectrical" <Alectrical@discussions.microsoft.com> wrote in message
> news:DDB54BE4-BC29-4A54-B990-29836E0FD0F3@microsoft.com...
> > Hi
> >
> > Can anyone tell me how to sum a range of numbers in column D, provided
> > that
> > adjacent cells in row A contain "Apples" and row C contain "Oranges".
> >
> > Thanks
> > Alec
>
>
>

Do you have text in H13:H6000? IF so, correct it or use my format.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Alectrical" <Alectrical@discussions.microsoft.com> wrote in message
news:2FB03CF0-16D3-4C6E-BBB0-134F3C7DD3EB@microsoft.com...
> Thanks Andy, your function works on a new sheet, but when I type the
> following function into an existing sheet I get the #VALUE message. Any
ideas.
>
> =SUMPRODUCT((A13:A6000="IO")*(F13:F6000="2PLC")*(H13:H6000))
>
> "Andy" wrote:
>
> > Hi
> >
> > Try something like this:
> >
> > =SUMPRODUCT((A2:A1000="Apples")*(C2:C1000="Oranges")*(D2:D1000))
> >
> > When using Sumproduct, make sure that the ranges are all the same size
and
> > are not full columns.
> >
> > Hope this helps.
> > Andy.
> >
> > "Alectrical" <Alectrical@discussions.microsoft.com> wrote in message
> > news:DDB54BE4-BC29-4A54-B990-29836E0FD0F3@microsoft.com...
> > > Hi
> > >
> > > Can anyone tell me how to sum a range of numbers in column D, provided
> > > that
> > > adjacent cells in row A contain "Apples" and row C contain "Oranges".
> > >
> > > Thanks
> > > Alec
> >
> >
> >

Thanks Bob, you've been very helpful



"Bob Phillips" wrote:

> Do you have text in H13:H6000? IF so, correct it or use my format.
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Alectrical" <Alectrical@discussions.microsoft.com> wrote in message
> news:2FB03CF0-16D3-4C6E-BBB0-134F3C7DD3EB@microsoft.com...
> > Thanks Andy, your function works on a new sheet, but when I type the
> > following function into an existing sheet I get the #VALUE message. Any
> ideas.
> >
> > =SUMPRODUCT((A13:A6000="IO")*(F13:F6000="2PLC")*(H13:H6000))
> >
> > "Andy" wrote:
> >
> > > Hi
> > >
> > > Try something like this:
> > >
> > > =SUMPRODUCT((A2:A1000="Apples")*(C2:C1000="Oranges")*(D2:D1000))
> > >
> > > When using Sumproduct, make sure that the ranges are all the same size
> and
> > > are not full columns.
> > >
> > > Hope this helps.
> > > Andy.
> > >
> > > "Alectrical" <Alectrical@discussions.microsoft.com> wrote in message
> > > news:DDB54BE4-BC29-4A54-B990-29836E0FD0F3@microsoft.com...
> > > > Hi
> > > >
> > > > Can anyone tell me how to sum a range of numbers in column D, provided
> > > > that
> > > > adjacent cells in row A contain "Apples" and row C contain "Oranges".
> > > >
> > > > Thanks
> > > > Alec
> > >
> > >
> > >
>
>
>

Hi

You must have a text value in your H range.

Andy.

"Alectrical" <Alectrical@discussions.microsoft.com> wrote in message
news:2FB03CF0-16D3-4C6E-BBB0-134F3C7DD3EB@microsoft.com...
> Thanks Andy, your function works on a new sheet, but when I type the
> following function into an existing sheet I get the #VALUE message. Any
> ideas.
>
> =SUMPRODUCT((A13:A6000="IO")*(F13:F6000="2PLC")*(H13:H6000))
>
> "Andy" wrote:
>
>> Hi
>>
>> Try something like this:
>>
>> =SUMPRODUCT((A2:A1000="Apples")*(C2:C1000="Oranges")*(D2:D1000))
>>
>> When using Sumproduct, make sure that the ranges are all the same size
>> and
>> are not full columns.
>>
>> Hope this helps.
>> Andy.
>>
>> "Alectrical" <Alectrical@discussions.microsoft.com> wrote in message
>> news:DDB54BE4-BC29-4A54-B990-29836E0FD0F3@microsoft.com...
>> > Hi
>> >
>> > Can anyone tell me how to sum a range of numbers in column D, provided
>> > that
>> > adjacent cells in row A contain "Apples" and row C contain "Oranges".
>> >
>> > Thanks
>> > Alec
>>
>>
>>

Thanks again Andy, you've been very helpful


"Andy" wrote:

> Hi
>
> You must have a text value in your H range.
>
> Andy.
>
> "Alectrical" <Alectrical@discussions.microsoft.com> wrote in message
> news:2FB03CF0-16D3-4C6E-BBB0-134F3C7DD3EB@microsoft.com...
> > Thanks Andy, your function works on a new sheet, but when I type the
> > following function into an existing sheet I get the #VALUE message. Any
> > ideas.
> >
> > =SUMPRODUCT((A13:A6000="IO")*(F13:F6000="2PLC")*(H13:H6000))
> >
> > "Andy" wrote:
> >
> >> Hi
> >>
> >> Try something like this:
> >>
> >> =SUMPRODUCT((A2:A1000="Apples")*(C2:C1000="Oranges")*(D2:D1000))
> >>
> >> When using Sumproduct, make sure that the ranges are all the same size
> >> and
> >> are not full columns.
> >>
> >> Hope this helps.
> >> Andy.
> >>
> >> "Alectrical" <Alectrical@discussions.microsoft.com> wrote in message
> >> news:DDB54BE4-BC29-4A54-B990-29836E0FD0F3@microsoft.com...
> >> > Hi
> >> >
> >> > Can anyone tell me how to sum a range of numbers in column D, provided
> >> > that
> >> > adjacent cells in row A contain "Apples" and row C contain "Oranges".
> >> >
> >> > Thanks
> >> > Alec
> >>
> >>
> >>
>
>
>