Counting Values Within and Outside of a Range

[Mvaldesi]

Good afternoon, Experts and Gurus!

I’ve got a list of POs, and I want to count those within the range of 100000000000 and 100009999999, then those outside of that range. I can get the former to work just fine; it’s the latter that’s evading me. I’m 99.9999% sure the answer is staring me right in the face (that it’s yet again my missing the “AND” forest for the “OR” trees as I am often wont to do,) but I can’t for the life of me suss it out. Little help?

[63falcondude]

Formula: =COUNTIFS($A$2:$A$8,"> 100000000000",$A$2:$A$8,"< 100009999999")
In words: Count the number of cells in A2:A8 that are greater than 100000000000 AND less than 100009999999.

If you want to count the number of cells in A2:A8 that are outside of that range, that would look like this:

Formula: =SUM(COUNTIFS($A$2:$A$8,{"<= 100000000000",">= 100009999999"}))
In words: Count the number of cells in A2:A8 that are less than or equal to 100000000000 OR are greater than or equal to 100009999999.

[Mvaldesi]

Yes, that most certainly does work; thank you!! Curious that it's more complex than I thought it'd be; I didn't expect I'd need to use an array, i.e.: I've never successfully used one that wasn't in a solution provided by someone on this forum!

[dominicb]

Good evening Mvaldesi

An alternative simpler option is to work out all numbers below 100000000000, and add to all numbers above 100009999999, thus :

Please Login or Register  to view this content.

HTH

DominicB

[Mvaldesi]

Good evening Mvaldesi

An alternative simpler option is to work out all numbers below 100000000000, and add to all numbers above 100009999999, thus :

Please Login or Register  to view this content.

HTH

DominicB

See, I knew I was overthinking it. This is the solution I probably would have eventually stumbled upon eventually, but I get so fixated on figuring out why my initially thought process doesn't work, I get in my own way. Thank you!

[63falcondude]

Thanks for the rep!

The reason for the array is to avoid a COUNTIFS()+COUNTIFS() formula (like the one in post #4). Both will work just fine.

The thing to keep in mind is that COUNTIFS acts as an AND operator when you have multiple criteria.

The formula that you had in C3 counted the number of cells in A2:A8 that are greater than 100000000000 AND less than 100009999999.

Since 100000000000 < 100009999999, that formula will always return 0 because there is no number that is both greater than 100000000000 AND less than 100009999999.

[Mvaldesi]

It always makes perfect and obvious sense when one of you Gurus or Experts explain it to me; thank you both!

[63falcondude]

Glad we could help!