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]

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.

The criterion for cells in A1:A500 is abc-01 that has 6 characters, therefore if LEN(A16)=10 it doesn't meet the criterion.

M.

 

[default_name]

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.

The data that I am working with is proprietary information that I am unable to disclose.
I used "abc-01" as an example. Sorry, I didnt think the actual data mattered.

The part numbers have the following format:
########-# or ########-##

Eight numeric digits, one hyphen, and one to two final digits (depending on part number).

 

[Rick Rothstein]

The data that I am working with is proprietary information that I am unable to disclose.

The part numbers have the following format:
########-# or ########-##

Eight numeric digits, one hyphen, and one to two final digits (depending on part number).

And the formula you are attempting to use?

 

[default_name]

And the formula you are attempting to use?

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

Substitute [abc-01] with the actual part number.

 

[default_name]

Cells A1:A15 are counted as expected.
Cells A17:A51 are counted as expected.
Cell A16 doesnt affect the count at all (even though the exact same part number in A16 is also seen in A3, A17, A26 and several others)

It is as if Excel just skipped over cell A16.

 

[Rick Rothstein]

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

Substitute [abc-01] with the actual part number.

Cells A1:A15 are counted as expected.
Cells A17:A51 are counted as expected.
Cell A16 doesnt affect the count at all (even though the exact same part number in A16 is also seen in A3, A17, A26 and several others)

It is as if Excel just skipped over cell A16.

Okay, so you are using a COUNTIFS function call which means every argument in it must be true in order for the count to register. You have established that A16 contains a properly formatted part number which must mean one of the other two conditions you are testing must be false. So, with that said, what is in cell B16 and what is in cell F16?

 

[Marcelo Branco]

The data that I am working with is proprietary information that I am unable to disclose.
I used "abc-01" as an example. Sorry, I didnt think the actual data mattered.

The part numbers have the following format:
########-# or ########-##

Eight numeric digits, one hyphen, and one to two final digits (depending on part number).

Maybe with a wildcard. See if this works - example to count part number = 12345678
=COUNTIFS(A1:A500,"12345678-*",B1:B500,">=" &F1-4.99,B1:B500,"<" & F1+4.99)

or better
=COUNTIFS(A1:A500,C2&"-*",B1:B500,">=" &F1-4.99,B1:B500,"<" & F1+4.99)
where C2 = 12345678

M.

 

[default_name]

This is getting pretty detailed, and I understand if it has become overly complex without being able to show the actual values.

Here is a scenario I just created to hopefully illustrate what the issue I am having is.
---------------------------

I have the following list of part numbers.
I will be recording the frequency/number of times a certain part number appears and the bin/value of the occurrence.

I am using the following formula to target the number of times Part# 12345678-04 appears:

=COUNTIFS($A$2:$A$19,"12345678-04",$B$2:$B$19,">=" &C2-4.99,$B$2:$B$19,"<" & C2+4.99)

This code is mimicked with the corresponding C values all the way down column D.

As you can see, all looks well, except the part number that appears at cell A16.
For some reason, cell A16 is being ignored (is not appearing in the Part Frequency list).

Notice how Part# 12345678-04 appears 3 times in Column A.
However, it is only being accounted for 2 times in Column D.
(A value of 1 should appear at Cell D10)

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Part Number
[/TD]
[TD="align: center"]Part Performance
[/TD]
[TD="align: center"]Bin
[/TD]
[TD="align: center"]Part Frequency
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]12345678-01
[/TD]
[TD="align: center"]81
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD][TABLE="width: 96"]
<colgroup><col style="text-align: center;" width="96"></colgroup><tbody>[TR]
[TD="class: xl65, width: 96"]12345678-03
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]27
[/TD]
[TD="align: center"]10
[/TD]
[TD="align: center"]0
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD][TABLE="width: 96"]
<colgroup><col style="text-align: center;" width="96"></colgroup><tbody>[TR]
[TD="class: xl65, width: 96"]12345678-02
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]83
[/TD]
[TD="align: center"]20
[/TD]
[TD="align: center"]1
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD][TABLE="width: 96"]
<colgroup><col style="text-align: center;" width="96"></colgroup><tbody>[TR]
[TD="class: xl65, width: 96"]12345678-04
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]21
[/TD]
[TD="align: center"]30
[/TD]
[TD="align: center"]0
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD][TABLE="width: 96"]
<colgroup><col style="text-align: center;" width="96"></colgroup><tbody>[TR]
[TD="class: xl65, width: 96"]12345678-01
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]5
[/TD]
[TD="align: center"]40
[/TD]
[TD="align: center"]0
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD][TABLE="width: 96"]
<colgroup><col style="text-align: center;" width="96"></colgroup><tbody>[TR]
[TD="class: xl65, width: 96"]12345678-03
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]86
[/TD]
[TD="align: center"]50
[/TD]
[TD="align: center"]0
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD][TABLE="width: 96"]
<colgroup><col style="text-align: center;" width="96"></colgroup><tbody>[TR]
[TD="class: xl65, width: 96"]12345678-04
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]92
[/TD]
[TD="align: center"]60
[/TD]
[TD="align: center"]0
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD][TABLE="width: 96"]
<colgroup><col style="text-align: center;" width="96"></colgroup><tbody>[TR]
[TD="class: xl65, width: 96"]12345678-02
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]13
[/TD]
[TD="align: center"]70
[/TD]
[TD="align: center"]0
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD][TABLE="width: 96"]
<colgroup><col style="text-align: center;" width="96"></colgroup><tbody>[TR]
[TD="class: xl65, width: 96"]12345678-03
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]30
[/TD]
[TD="align: center"]80
[/TD]
[TD="align: center"]0
[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD][TABLE="width: 96"]
<colgroup><col style="text-align: center;" width="96"></colgroup><tbody>[TR]
[TD="class: xl65, width: 96"]12345678-01
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]90
[/TD]
[TD="align: center"]1
[/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD][TABLE="width: 96"]
<colgroup><col style="text-align: center;" width="96"></colgroup><tbody>[TR]
[TD="class: xl65, width: 96"]12345678-02
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]53
[/TD]
[TD="align: center"]100
[/TD]
[TD="align: center"]0
[/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD][TABLE="width: 96"]
<colgroup><col style="text-align: center;" width="96"></colgroup><tbody>[TR]
[TD="class: xl65, width: 96"]12345678-03
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]81
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD][TABLE="width: 96"]
<colgroup><col style="text-align: center;" width="96"></colgroup><tbody>[TR]
[TD="class: xl65, width: 96"]12345678-01
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]94
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15
[/TD]
[TD][TABLE="width: 96"]
<colgroup><col style="text-align: center;" width="96"></colgroup><tbody>[TR]
[TD="class: xl65, width: 96"]12345678-02
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]58
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16
[/TD]
[TD][TABLE="width: 96"]
<colgroup><col style="text-align: center;" width="96"></colgroup><tbody>[TR]
[TD="class: xl65, width: 96"]12345678-04
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]80
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17
[/TD]
[TD][TABLE="width: 96"]
<colgroup><col style="text-align: center;" width="96"></colgroup><tbody>[TR]
[TD="class: xl65, width: 96"]12345678-01
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]73
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18
[/TD]
[TD][TABLE="width: 96"]
<colgroup><col style="text-align: center;" width="96"></colgroup><tbody>[TR]
[TD="class: xl65, width: 96"]12345678-02
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]31
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19
[/TD]
[TD][TABLE="width: 96"]
<colgroup><col style="text-align: center;" width="96"></colgroup><tbody>[TR]
[TD="class: xl65, width: 96"]12345678-03
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"]66
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

The formula worked for me


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Part Number​

[/TD]
[TD]

Part Performance​

[/TD]
[TD]

Bin​

[/TD]
[TD]

Part Frequency​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

12345678-01​

[/TD]
[TD]

81​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

12345678-03​

[/TD]
[TD]

27​

[/TD]
[TD]

10​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

12345678-02​

[/TD]
[TD]

83​

[/TD]
[TD]

20​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD="bgcolor: #FFFF00"]

12345678-04​

[/TD]
[TD]

21​

[/TD]
[TD]

30​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

12345678-01​

[/TD]
[TD]

5​

[/TD]
[TD]

40​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

12345678-03​

[/TD]
[TD]

86​

[/TD]
[TD]

50​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD="bgcolor: #FFFF00"]

12345678-04​

[/TD]
[TD]

92​

[/TD]
[TD]

60​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

12345678-02​

[/TD]
[TD]

13​

[/TD]
[TD]

70​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

12345678-03​

[/TD]
[TD]

30​

[/TD]
[TD]

80​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

11​

[/TD]
[TD]

12345678-01​

[/TD]
[TD]

1​

[/TD]
[TD]

90​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

12​

[/TD]
[TD]

12345678-02​

[/TD]
[TD]

53​

[/TD]
[TD]

100​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

13​

[/TD]
[TD]

12345678-03​

[/TD]
[TD]

81​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

14​

[/TD]
[TD]

12345678-01​

[/TD]
[TD]

94​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

15​

[/TD]
[TD]

12345678-02​

[/TD]
[TD]

58​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

16​

[/TD]
[TD="bgcolor: #FFFF00"]

12345678-04​

[/TD]
[TD]

80​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

17​

[/TD]
[TD]

12345678-01​

[/TD]
[TD]

73​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

18​

[/TD]
[TD]

12345678-02​

[/TD]
[TD]

31​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

19​

[/TD]
[TD]

12345678-03​

[/TD]
[TD]

66​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


D2 copied down
=COUNTIFS($A$2:$A$19,"12345678-04",$B$2:$B$19,">=" &C2-4.99,$B$2:$B$19,"<" & C2+4.99)

M.

 

[default_name]

This is where my issue is. The cell should be evaluating, but it isn’t for some reason.

No matter what I put on A16, whether it be “cat” or even a string of numbers (with the corresponding COUNTIFS update statement), the function is not reading the cell.

My question here is why. I have tried clearing all formatting on the cell. I have tried deleting the cell entirely and inserting a blank one. I can’t seem to pinpoint WHY this cell is being overlooked. I don’t see any other references to the cell in the workbook except here.