Count cells in table with specific criteria

[drom]

Hi and thanks in advance!


I have a table named Table1 with many rows and columns

The DataBodyRange of this table contains always values such as

• 45658ax
• 12ax
• 9878ax
• 887412ax
• 557ax

So they have always a string "ax" at the end of the cell

I would like to count how many of those cells have a value > 1000

In my eg ig If have only the above cells...

The result should be = 3

?

 

[drom]

The Table is named aaa, cell B2 is Wrong (was just for this eg) ?
I do not know how to avoid #VALUE!
the answer should be 1 (Red Text) but the #VALUE! avoids the this

 

[Fluff]

How about

=SUMPRODUCT((RIGHT(aaa,2)="ax")*(SUBSTITUTE(aaa&"ax","ax","0")/100>70000))