## Hard coding combo of cell references and operators in functiosns

iksotof
3StarLounger
Posts: 318
Joined: 04 May 2010, 15:18

### Hard coding combo of cell references and operators in functiosns

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
Last edited by iksotof on 02 Jul 2024, 15:10, edited 1 time in total.

HansV
Posts: 79321
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

### Re: Hard coding combo of cell references and operators in functiosns

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

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

HansV
Posts: 79321
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

### Re: Hard coding combo of cell references and operators in functiosns

Alternatively:

=SUMPRODUCT((C11:C21>=A1)*(C11:C21<=A2))
Best wishes,
Hans

iksotof
3StarLounger
Posts: 318
Joined: 04 May 2010, 15:18

### Re: Hard coding combo of cell references and operators in functiosns

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