Dec 27 2023 09:52 AM
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 that work has completed. I am trying to find a way to find the total number of POs (both active and completed) that are between the highest and lowest active PO numbers.
=MAX and =MIN works for the active list of POs
=COUNTIFS only seems to work if the criteria is a number not a nested formula
Dec 27 2023 12:05 PM
Can you provide a more specific description? COUNTIFS should work with expressions (formulas) in the criteria arguments too.
Dec 28 2023 05:24 AM
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.
Dec 28 2023 07:20 AM
Thanks! Try this version, with the cell references taken outside the quoted strings:
=COUNTIFS(WIP!C3:C183,">="&C37,WIP!C3:C183,"<="&C36)
Dec 28 2023 08:46 AM
Dec 28 2023 11:25 AM
& 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.
Dec 28 2023 11:32 AM