SUMIF INDIRECT

A

Alectrical

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
 
B

Bob Phillips

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
G

Guest

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.
 
A

Alectrical

Thanks Bob

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
Click to expand...
 
A

Alectrical

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

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

Alectrical

Thanks Bob, you've been very helpful



Bob Phillips said:
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)
Click to expand...
 
Top