Can I count cells between number thresholds if the cell also has text in it?

Question

Hello!

I'm currently creating formulas for a gift register to document the price ranges we have and how many are in each category. My categories are:

£0 - £50

£50 - £100

£100 - £200

£200 +

However, the formulas aren't pulling back all the data accurately because some of the cells include text too like 'less than £500' or '£200+ plus vat'. Is there a formula that can still count the number in the cell, and ignore the words?

Any help would be greatly appreciated!!

Levon · Answer

Hi,

If this is a one-time cleanup activity, the following steps may help. Ultimately, having the prices come into your sheet in a consistent format is probably the way to go.

I've created an example sheet and made a few assumptions.

1. The price will always be preceded by a "£" sign.

2. After the last digit, there is either a space or nothing.

3. The formula assumes that boundary values (£50, £100, £200) belong to the upper band. An Item priced exactly £50 is counted in £50 - £100, not £0 - £50. If that's not what you want, change the first condition from < 50 to <= 50 and adjust accordingly.

Notice in my example, the formula fails on £200+ plus VAT style entries where a + immediately follows the number.

Does this get you on the right path? Happy to share the demo sheet with you.

Paul Newcome · Answer

What formulas are you currently using?