how to find errors or text that contain "#" in a data set

numbers2023

New Member
Joined
Jun 1, 2023
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hi All,
I have a data range where I want to find out if there is a cell in error by including the search term "#" in it, but I used COUNTIF and ISERROR but got a #SPILL error.
I landed on this formula set, but it's not returning correct results, meaning I manually entered a text value in the range, but it still shows that it's ok. Any ideas on how to correct this?

This is the formula I'm using: =IF(SUMPRODUCT(--ISNUMBER('EL Retrieve'!$B$11:$BR$152))>0,"Yes","No") but this works if it's a positive number, but if it's a negative number or text, it's not picking it up.

Here is my result:
1685718126528.png


Essentially, I just want to create a formula that will detect if any of the cells in the data range B11:BR152 have a # in it.

How can I do this? Thanks.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Is it IFERROR you should be using or am i off the mark?
Book3
BCD
11#NAME?ERROR
12#DIV/0!ERROR
Sheet2
Cell Formulas
RangeFormula
D11:D12D11=IFERROR(B11:B12,"ERROR")
B11B11=SUM(a,b)
B12B12=125/0
Dynamic array formulas.
 
Upvote 0
Thanks @Kerryx when I entered your formula above:
=IFERROR('Real Estate Retrieve'!B11:BR152,"error")

this is what I get in the results:
1685720064111.png


What else can I try?
 
Upvote 0
How about
Excel Formula:
=IF(COUNTIFS('Real Estate Retrieve'!B11:BR152,"*"),"Yes","No")
This will return yes if any of the cells contain text.
 
Upvote 0
Solution
Glad we could help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,223,239
Messages
6,170,947
Members
452,368
Latest member
jayp2104

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top