Complicated Conditional Data

default_name

Board Regular
Joined
May 16, 2018
Messages
80
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),"")
Thanks in advance!
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,977
Another option:

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

default_name

Board Regular
Joined
May 16, 2018
Messages
80
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
Joined
Apr 18, 2011
Messages
35,504
Office Version
2010
Platform
Windows
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
Joined
May 16, 2018
Messages
80
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
Joined
Apr 18, 2011
Messages
35,504
Office Version
2010
Platform
Windows
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
Joined
May 16, 2018
Messages
80
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
Joined
Apr 18, 2011
Messages
35,504
Office Version
2010
Platform
Windows
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.
 

Forum statistics

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

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top