Sumproduct or Other Formula?

[Rob]

Hi,

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

=SUMPRODUCT(--(D15<10),(D812)) 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 D812 i.e. cells D9 and D11 which will
add up to 40 (25 and 15).

Thanks, Rob

 

[Bob Phillips]

=SUM(IF(ISNUMBER(MATCH(C8:C12,IF(D15<10,C1:C5),0)),D812))

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

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[Rob]

Thanks Bob, once again your knowledge is remarkable.

Rob