Complicated Conditional Data

[default_name]

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:

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

Thanks in advance!

 

[Marcelo Branco]

Maybe...

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

M.

 

[Eric W]

Another option:

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

 

[default_name]

Works perfectly!!
Thanks guys!

Quick response too! Much appreciated!

 

[default_name]

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]

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]

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]

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]

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.

 

[Rick Rothstein]

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.