gsheets

GSheets countif formula when the cell contains two numbers

this is the formula im using: =COUNTIF(B$2:B$6,"<=3")

this are the cells im trying to count to:

enter image description here

What I want to happen:

count in range all those within 1-3. so the result of the formula should be 5. but the formula can't read the 1 & 8 in the first cell.

how should i go about this? regex or something else?

Solution

  • If you're trying to count all the values that are below 3, you can write the formula as =SUM(COUNTIF(ARRAYFORMULA(SPLIT(B2:B6, " & ")),"<=3"))

    First thing to do is to split the numbers by & and convert the result to array. Once that is done, you can use COUNTIF to count and get a sum of the count values.