# Complicated Conditional Data

#### default_name

##### Board Regular
I am trying to write a function that looks through the data with the following characteristics:
If the range of the data (cells A1:A500) has part number "abc-01" then I want to look at the value for that part (values found in range B1:B500) and see whether the value falls within 4.99 of a referenced cell (F1).

Basically, I am trying to count the frequency of a referenced value for a particular part in a large list of different part numbers.
(Per the security policy on my machine, I cannot use VBA/Macros.)

Not sure my explanation makes sense. Here's the code I have:

Code:
``=IF(A1:A500="abc-01",COUNTIFS(B1:B500,">=" &F1-4.99,B1:B500,"<" & F1+4.99),"")``

#### Marcelo Branco

##### MrExcel MVP
Maybe...

=COUNTIFS(A1:A500,"abc-01",B1:B500,">=" &F1-4.99,B1:B500,"<" & F1+4.99)

M.

Last edited:

#### Eric W

##### MrExcel MVP
Another option:

=SUMPRODUCT(--(A1:A500="abc-01"),--(ABS(B1:B500-F1)<=4.99))

Last edited:

#### default_name

##### Board Regular
Works perfectly!!
Thanks guys!

Quick response too! Much appreciated!

#### default_name

##### Board Regular
Additional problem: Not sure if I need to make a new post for this or not.

There are a few cells that are not being accounted for in the calculation. I used a SUM function to check to make sure everything was being counted...discovered that there are a few that are not being accounted for.

I looked into some of the cells and discovered that, A16 is not being counted in the equation.
Very odd. I have cleared contents, deleted the cell, inserted a blank cell, and checked formatting. It is as if cell A16 is invisible.

How do I go about finding the rest of these 'ghost' cells and fixing this error?

Thanks again!

#### Rick Rothstein

##### MrExcel MVP
There are a few cells that are not being accounted for in the calculation. I used a SUM function to check to make sure everything was being counted...discovered that there are a few that are not being accounted for.

I looked into some of the cells and discovered that, A16 is not being counted in the equation.
Very odd. I have cleared contents, deleted the cell, inserted a blank cell, and checked formatting. It is as if cell A16 is invisible.

How do I go about finding the rest of these 'ghost' cells and fixing this error?
I would be willing to bet that cell A16 has a space character at the end of the text. You can test this out with this formula...

=LEN(A16)

and see if the value returns is greater than 6.

#### default_name

##### Board Regular
Sorry about reposting in a new thread (I was unsure if a seeming unrelated new issue needed a new post or not).

I ran =LEN(A16)
It returned a 0.

There aren't any merged cells here either.

#### Rick Rothstein

##### MrExcel MVP
I ran =LEN(A16)
It returned a 0.
Well, that means cell A16 is empty. What exactly did you mean when you said "I looked into some of the cells and discovered that, A16 is not being counted in the equation"... why would you think an empty cell should have been counted when searching for something other than empty cells?

#### default_name

##### Board Regular
Well, that means cell A16 is empty. What exactly did you mean when you said "I looked into some of the cells and discovered that, A16 is not being counted in the equation"... why would you think an empty cell should have been counted when searching for something other than empty cells?

Sorry, I had cleared the cell to try and see if there were issues with the cell itself.
=LEN(A16) results in a 0 here

When I put the data back, it results in a 10.
A 10 makes sense here because this part number is 10 digits long.

However, this cell is still being ignored in the COUNTIF function.

Last edited:

#### Rick Rothstein

##### MrExcel MVP
Sorry, I had cleared the cell to try and see if there were issues with the cell itself.
=LEN(A16) results in a 0 here

When I put the data back, it results in a 10.
A 10 makes sense here because this part number is 10 digits long.
I thought the part number had the format AAA-NN where A is an alpha character and N is digit character? Maybe if you posted what your data actually looks like and the actual formula you are using (that you say does not recognize what is in cell A16), we may have a better chance of being able to help you.

1,081,981
Messages
5,362,535
Members
400,679
Latest member
alecalec202

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...