Count how many cells are between two values

Mary Shevlin

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

M Underbrink

Try this

=COUNTIFS({Export Range 5}, <30, {Export Range 5}, <120)

M Underbrink

Try this

=COUNTIFS({Export Range 5}, <30, {Export Range 5}, <120)

Mary Shevlin

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

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

Thank you M Underbrink

Cleversheet

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.

Thank you so much for sharing, @Cleversheet!