Hi,

Is Sumproduct the formula I need, and if so, how does it work where the
range is not in the same order?

=SUMPRODUCT(--(D1:D5<10),(D8:D12)) works on the example below but only when
ranges are in same order which I can't do in this situation.

I have two ranges in a spreadsheet, the first is cells C1 to C5 and the
contents is text Test1, Test2, Test3, Test4 and Test5, the adjacent cells D1
to D5 have numbers 5, 10, 5, 10 and 10. My other range is C8 to C12 and
contains Test2, Test1, Test4, Test3 and Test5 (same as in cells C1 to C5 but
in different order). My fourth range is D8 to D 12 and the contents is 12,
25, 34, 15, 34.

I need a formula that identifies the cells in range D1 to D5 as being less
than 10 i.e. D1 and D3, this also identifies the contents in column C as
Test1 and Test3. Using Test1 and Test3, look up this in range C8:C12 and
sum the adjacent numbers in range D8:D12 i.e. cells D9 and D11 which will
add up to 40 (25 and 15).

Thanks, Rob

=SUM(IF(ISNUMBER(MATCH(C8:C12,IF(D1:D5<10,C1:C5),0 )),D8:D12))

as an array formula, so commit with Ctrl-Shift-Enter, not just Enter.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
"Rob" wrote in message
...
Hi,

Is Sumproduct the formula I need, and if so, how does it work where the
range is not in the same order?

=SUMPRODUCT(--(D1:D5<10),(D8:D12)) works on the example below but only
when ranges are in same order which I can't do in this situation.

I have two ranges in a spreadsheet, the first is cells C1 to C5 and the
contents is text Test1, Test2, Test3, Test4 and Test5, the adjacent cells
D1 to D5 have numbers 5, 10, 5, 10 and 10. My other range is C8 to C12
and contains Test2, Test1, Test4, Test3 and Test5 (same as in cells C1 to
C5 but in different order). My fourth range is D8 to D 12 and the
contents is 12, 25, 34, 15, 34.

I need a formula that identifies the cells in range D1 to D5 as being less
than 10 i.e. D1 and D3, this also identifies the contents in column C as
Test1 and Test3. Using Test1 and Test3, look up this in range C8:C12 and
sum the adjacent numbers in range D8:D12 i.e. cells D9 and D11 which will
add up to 40 (25 and 15).

Thanks, Rob

Thanks Bob, once again your knowledge is remarkable.

Rob
"Bob Phillips" wrote in message
...
=SUM(IF(ISNUMBER(MATCH(C8:C12,IF(D1:D5<10,C1:C5),0 )),D8:D12))

as an array formula, so commit with Ctrl-Shift-Enter, not just Enter.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
"Rob" wrote in message
...
Hi,

Is Sumproduct the formula I need, and if so, how does it work where the
range is not in the same order?

=SUMPRODUCT(--(D1:D5<10),(D8:D12)) works on the example below but only
when ranges are in same order which I can't do in this situation.

I have two ranges in a spreadsheet, the first is cells C1 to C5 and the
contents is text Test1, Test2, Test3, Test4 and Test5, the adjacent cells
D1 to D5 have numbers 5, 10, 5, 10 and 10. My other range is C8 to C12
and contains Test2, Test1, Test4, Test3 and Test5 (same as in cells C1 to
C5 but in different order). My fourth range is D8 to D 12 and the
contents is 12, 25, 34, 15, 34.

I need a formula that identifies the cells in range D1 to D5 as being
less than 10 i.e. D1 and D3, this also identifies the contents in column
C as Test1 and Test3. Using Test1 and Test3, look up this in range
C8:C12 and sum the adjacent numbers in range D8:D12 i.e. cells D9 and D11
which will add up to 40 (25 and 15).

Thanks, Rob