Reverse of sumproduct : product of product of array?

Hi,

I have two columns of data, with the following unique values in A1:A6

Please Login or Register  to view this content.

and the following in B1:B6

Please Login or Register  to view this content.

In C1:C2 and in C4 I have three criteria

Please Login or Register  to view this content.

And finally I use the following array formula to try to find the product of cells in column B that are in the rows of cells in column A that match criteria in C1:C2 and C4.

Formula:

Please Login or Register  to view this content.

If I use SUM() instead of PRODUCT(), it returns the correct value (3.5), but the above formula returns 0. I want it to return 2*0,5*1 = 1.

Originally Posted by CK76

Try...
=PRODUCT(IF(ISNUMBER(SEARCH(C1:C6,B1:B6)),B1:B6,""))

Confirmed as array (CTRL + SHIFT + ENTER)

Thanks, this seemed to work at first but a couple things:

• It's important (for where this fits into my less simplified spreadsheet) that the criteria range is discontiguous, so something like {C1:C2;C4} etc., not {C1:C6}.
• The formula doesn't work if I use different example letters for the criteria (e.g. C1="A", C2="D", C4="C" which should have product of 1*0,125*2=0,25, but returns 1), or if I move the criteria range around (C1="C", C2="F", C5="B" which should be 2*0,5*1=1, but returns 0,125).

Originally Posted by CK76

Oh, had typo.
=PRODUCT(IF(ISNUMBER(SEARCH(C1:C6,A1:A6)),B1:B6,""))

Edit: Oh I see. I had misread. Why the non-contiguous range for criteria?

Yeah I assumed you meant this and edited it like above, but still not working properly as described.

Discontiguous because I have criteria used as other things in a big table and it'd be a hassle to move them to be adjacent without breaking other things.

Originally Posted by CK76

I've edited previous post. Check the new formula and see if that works.

Your edited formula seems to work well, thanks very much.