Trying to use dynamic operators in Sumproduct formulas with Pull-Down menus

[Gretschman]

I am having a lot of trouble here, consider the following:

=SUMPRODUCT(($AH$15:$AH$63>=$C93)*($AH$15:$AH$63<$C94)*($Y$15:$Y$63>=0.8))

In this formula Column AH represents Due Dates, cells C93 and C94 return dates between 1/1/14 and 1/1/15, and column Y returns loans with ratios >= 80% for due dates in 2014. I would like to have my users able to specify the operator (i.e. <,>,<=, etc.) from a pull-down menu in Cell D145 and type a ratio target (say .8 for 80% or 1 for 100%) into Cell E145.

I have tried to concatenate the results of cells D145 and E145 to replace the ">=" and .8 in this part of my formula ($Y$15:$Y$63>=0.8), allowing users to get results dynamically. I think I'm getting errors because Concatenation returns a string, but I cannot for the life of me figure out how to replace the variables in the equation using other cells as inputs (in this case, of course, D145 and E145.

Does anyone know how this can be done? I would very much appreciate the help of any Forum members who have dealt with problems like this one.

Thanks.

[FDibbins]

Hi and welcome to the forum

Have you tried using INDIRECT() in there?

If that doesnt work, maybe use nested IF()'s and duplicate the formula with different signs, to swap from 1 to the other?

[Olly]

Are you trying to count the matching rows?

Try a COUNTIFS:

Please Login or Register  to view this content.

Then you can choose an operator in cell D145, and a value in E145.

[Gretschman]

Thanks to both of you, the problem has been solved. I am so appreciative of your speedy replies!