Forum Discussion

Copper Contributor

Dec 27, 2023

copying an absolute value of a formula to a separate cell?

I am trying to determine the number of items in a changing range that are between a changing high and low number. - I have a list of current POs that are actively being worked on and a list of POs th...

excel

Formulas and Functions

dtruhe

Copper Contributor

Dec 28, 2023

HansVogelaar

Here is the formula that is not working. It returns a value of 0

=COUNTIFS(WIP!C3:C183,">=(C37)",WIP!C3:C183,"<=(C36)")

WIP is Work in Progress page in the same folder

C37 and C36 are =MIN(WIP!C3:C48) and =MAX(WIP!C3:C48) which return the highest and lowest values of current work in progress

I have tried putting the MIN MAX formulas in place of C37 and C36 and it still returns a value of 0

Hans thank you in advance for your help with this.

HansVogelaar

MVP

Dec 28, 2023

dtruhe

Thanks! Try this version, with the cell references taken outside the quoted strings:

=COUNTIFS(WIP!C3:C183,">="&C37,WIP!C3:C183,"<="&C36)

dtruhe
Copper Contributor
Dec 28, 2023
That was it! Thank you! and, this may be a stupid question but, what do the ampersands do in front of C37 and C36
- HansVogelaar
  MVP
  Dec 28, 2023
  dtruhe
  
  & concatenates (combines) values. For example, "Broc"&"coli" results in "Broccoli".
  
  ">="&C37 concatenates ">=" with the value of cell C37.
  
  On the other hand, ">=(C37)" returns the literal text string ">=(C37)"; it does not use the value of cell C37.
  - dtruhe
    Copper Contributor
    Dec 28, 2023
    Awesome, thanks again!