ProductIF formula with multiple criteria

Hello,

I am trying to work out returns if a score falls into given class boundaries, using the ProductIF formula:

Here's what I am using in my simple example, which is attached:

{=PRODUCT(IF($D$3:$D$12>=H3,$D$3:$D$12<I3,1+$C$3:$C$12))}.

The data is:
Return Score
5.00% -0.35
-2.00% -0.34
3.00% -0.33
-0.25% -0.32
2.00% -0.31
1.00% -0.30
0.57% -0.29
-2.00% -0.28
0.72% -0.27
-0.92% -0.26

and the classes are:

Bottom limit Upper limit
-0.34 -0.29
-0.29 -0.24
-0.24 -0.19

Unfortunately, I keep getting the wrong answers (As shown in the attachment). Can anyone help?

Best regards,
Excel_Arate

I'm not sure what the TRUE clause of your IF function is trying to do. You have this:

IF($D$3:$D$12>=H3, $D$3:$D$12<I3, 1+$C$3:$C$12)

so you have a condition for column D being greater than or equal to H3 - if it's true then you have another condition for column D being less than I3. I suspect that you want both those conditions to apply through an AND, so perhaps your formula should be:

{=PRODUCT(IF(($D$3:$D$12>=H3)*($D$3:$D$12<I3),1+$C$3:$C$12))}

When you are dealing with arrays, the * symbol is equivalent to AND.

Hope this helps.

Pete