Hard coding combo of cell references and operators in functiosns

[iksotof]

Hello

I have a countifs construct where I want the citeria of costs to evaluate the number of occurences from one to another value (ie includ both the start and end and between values). I would like the function to take the lower and upper vaues from user keying and hard wire the operators into to the formula, how do I code them with cell references where the user keys the lower and upper boundaries, I am struggling a little:


This is my basic construct with the parameter in the function =COUNTIFS(C11:C21,">=50",C11:C21,"<=60") but I don't want the number parameter in the code, I want them taken from cell values, say A1 and A2, I know this isn't correct but how do I write this to make it work =COUNTIFS(C11:C21,">=A1",C11:C21,"<=A2")


Thank you

[HansV]

You have to place the cell references outside the quotes and concatenate them with the quoted string:

=COUNTIFS(C11:C21,">="&A1,C11:C21,"<="&A2)

[HansV]

Alternatively:

=SUMPRODUCT((C11:C21>=A1)*(C11:C21<=A2))

[iksotof]

Thank you Hans,

It's refreshing to know I tried that, i must have something else a miss in the formula, there are other multiple criteria. I will go back and evaluate and try to resolve

Best wishes