Count how many cells are between two values

Options

So, the first one was pretty easy

=COUNTIF({Export Range 5}, <30) - that works

But, now I need to count the number of cells between 31 & 120; I just can't seem to make it work.

=COUNTIF({Export Range 5}, >30 and <120) just gets me "unparseable".

=COUNTIF({Export Range 5}, >30, <120) gets me "incorrect argument set".

What am I doing wrong. I've spent a lot of time searching for an answer, so here I am.

Thank you for your help,

Mary

Tags:

Best Answer

Answers

  • Mary Shevlin
    Mary Shevlin ✭✭✭✭
    Options

    That would count the number under 30 (<30) as well - I want the number of cells greater than 30 and less than 120.

    I did try the formula, and got Incorrect Argument Set.

  • Mary Shevlin
    Mary Shevlin ✭✭✭✭
    Options

    Ok - I didn't notice the "s" - coundifS - I changed the < to and it looks like it works.

    Thank you M Underbrink

  • Cleversheet
    Cleversheet ✭✭✭✭✭✭
    Options

    Not exactly the same scenario, but perhaps similar enough that someone searching might look here. In this case I wanted to display in ResultA each value in OriginA multiplied by a different factor (found in RefSheetC) based on its position within the range limits specified in RefSheetB.

    The several BuilderX columns referenced here are simply for the purpose of illustrating the formula elements—how did we get there from here? All of them eventually get collapsed into the ResultA formula.

    OriginA contains values you want to operate on; in this example between 1 and 70, such as 26, 9, 57, ...

    RefSheetB has range limits 10, 20, 30, ... to 70.

    RefSheetC has corresponding factors 3.5, 3, 2.5, ... to 1

    BuilderD grabs the lowest range limit: =MIN(RefSheetB:RefSheetB)

    BuilderE grabs the lowest range limit: =MAX(RefSheetB:RefSheetB)

    BuilderF associates the OriginA value with its RefSheetB range limit by isolating the value of the tens digit and multiplying that by 10 (ie, in this case we want anything in the 20's to be multiplied by 3); note that there's no tens position to strip from an OriginA value under 10, so in that case the unstripped OriginA value is used; also, : =IF(OriginA@row <= BuilderD@row, BuilderD@row, IF(OriginA@row > BuilderE@row, BuilderE@row, (VALUE(LEFT(OriginA@row, 1)) * 10)))

    BuilderG identifies the applicable factor from RefSheetC by finding which range in RefSheetB applies to the calculated value in BuilderE (ie, that enables positioning the value from OriginA): =IFERROR(INDEX(RefSheetC:RefSheetC, MATCH(BuilderF@row, RefSheetB:RefSheetB, 0)), "")

    BuilderH produces the intended outcome by multiplying OriginA with its target RefSheetC factor: =[OriginA]@row * [BuilderG]@row

    ResultA distills all the builder columns into a single formula such that you don't actually need any of the builder columns; it can all be done in one step (the bolded portion that follows). As you'll see, I wrapped it in an IF to displays only the results that exceed 0, so when OriginA is blank ResultA will also be blank (instead of 0): =IF(OriginA@row * IFERROR(INDEX(RefSheetC:RefSheetC, MATCH(IF(OriginA@row <= MIN(RefSheetB:RefSheetB), MIN(RefSheetB:RefSheetB), IF(OriginA@row > MAX(RefSheetB:RefSheetB), MAX(RefSheetB:RefSheetB), (VALUE(LEFT(OriginA@row, 1)) * 10))), RefSheetB:RefSheetB, 0)), "") > 0, OriginA@row * IFERROR(INDEX(RefSheetC:RefSheetC, MATCH(IF(OriginA@row <= MIN(RefSheetB:RefSheetB), MIN(RefSheetB:RefSheetB), IF(OriginA@row > MAX(RefSheetB:RefSheetB), MAX(RefSheetB:RefSheetB), (VALUE(LEFT(OriginA@row, 1)) * 10))), RefSheetB:RefSheetB, 0)), ""))

    As intended, the values in ResultA show the product from multiplying OriginA by the RefSheetB-range-determined RefSheetC value. Obviously, these are all on the same sheet in this image, whereas "in real life" I'd store RefSheetB and RefSheetC in their own separate sheet—and all the builder columns are superfluous.

  • Genevieve P.
    Options

    Thank you so much for sharing, @Cleversheet!

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!