SUMIF INDIRECT

[Guest]

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

 

[Bob Phillips]

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

Hi

Try something like this:

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

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

Hope this helps.
Andy.

 

[Guest]

Thanks Bob

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

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

 

[Bob Phillips]

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)

 

[Guest]

Hi

You must have a text value in your H range.

Andy.

 

[Guest]

Thanks Bob, you've been very helpful

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)

 

[Guest]

Thanks again Andy, you've been very helpful

Hi

You must have a text value in your H range.

Andy.