Excel Formula logic issue

Question

I am encountering a formula issue when trying to pull some data from Excel. for ref cells in column G have a right formula (e.g. G3 is =right(E3,2)) the information it is pulling is a 2 digit number. from this cell J3 is running formula =COUNTIFS(G3:G32,">=14",G3:G32,"<=20") however this keeps coming up as 0 even though there is relevant data in the field. wondering what problem we are running into.

Answer 1

Hello @Ben Foubister,

Thanks for sharing the details.

At the moment, I can’t be completely certain whether the value you’re using with the RIGHT function contains only numeric characters or if it includes something that causes Excel to treat it as text. However, based on my testing, this behavior is expected in such cases.

I was able to reproduce the same result on my side using a data set that visually looks numeric but also contains text. When using the formula as‑is, the calculation returns 0, because Excel is treating the result as text, not a number.

To make sure the result is handled as a number, I recommend explicitly converting it by wrapping the formula with VALUE, for example:

=VALUE(RIGHT(E3,2))

This ensures the output of the RIGHT function is interpreted as a numeric value, and the calculation should then work as expected.

Please give this a try and let me know the result. If it still doesn’t behave as expected, feel free to reply back and we can continue troubleshooting together.

Kind regards,

---

If the answer is helpful, please click "Accepted" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

The COUNTIFS criteria are being applied to text values, not numbers.

RIGHT(E3,2) returns a text string like "14", "20", etc. COUNTIFS with numeric criteria (">=14", "<=20") will not work as expected on text, so the result is 0.

Convert the RIGHT result to a number, then count on that numeric range.

1. Change column G to return numbers instead of text, for example:

      G3: =VALUE(RIGHT(E3,2))
   

   Fill this down to G32.
2. Keep the COUNTIFS as-is:

      =COUNTIFS(G3:G32,">=14",G3:G32,"<=20")
   

If changing column G is not possible, wrap VALUE directly in COUNTIFS:

=COUNTIFS(VALUE(G3:G32),">=14",VALUE(G3:G32),"<=20")

(Use this only if the Excel version supports array formulas in this way; otherwise the first approach is recommended.)

Also ensure the cells in G3:G32 are not formatted as Text. If they are, change the format to General/Number and re-enter the formulas so Excel can calculate them correctly.

---

References:

• How to avoid broken formulas in Excel
• Detect formula errors in Excel