Check for Number in one column - then count specific numbers in a range.

[Carlos_m]

Guys I would appreciate any help you could offer me on this
(I spent 3 days searching about everyplace I can think of for a solution)

Column "B" has 75 items. Of those items 1 to 25 are numbered the rest are blank.
(All are needed, but only the newest 25 are relative).

Column "D" through "J" have numbers (75 per column).
I need to use the numbers in column "B" Only to "Authorize" searching D1:J75 for the number of occurrences of a specific number.
(The number in "B" is else-wise semi irrelevant)

The Numbers in column "B" will be re-ordered when "New" data is appended to future rows.

I have tried COUNTIF, COUNTIFS, SUMPRODUCT, IF-Then with no luck.
I am new(ish) to the more advanced features of Excel, but I am familiar with the program itself.
Since different versions can have slightly different formulas, I'm using Office 2010 on Win7.

Thanks - I Really appreciate it...

 

[L. Howard]

With:
Q7 = 1
B7 = 1

1. With this formula in cell R7 it does return 2.

=COUNTIF(INDIRECT("D7:L" & MATCH(TRUE,INDEX(ISBLANK($B$7:$B$496),0,0),0)+5),Q7)

Q7 (Number 1) R7 (Result) = 2

2. Tell me in plain speak what the 2 result in R7 is or represents. Why is it 2?

3. If B7 is blank, R column returns 0 (zero)


As for the "B" column "Numbers" are excluded.

What does this excluded statement mean. Excluded from what or where?

Howard

 

[jasonb75]

Cross post at Check for Number in one column - then count specific numbers in a range.

Please see rule #10 http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html and Excelguru Help Site - A message to forum cross posters

 

[jasonb75]

searching D1:J75 for the number of occurrences of a specific number.

I assume that this should say D7:H81?

Also, your occurrence counts in column R don't all match up with the information you have given us.

Given the inaccuracy of your example, this is my best guess based on what I think is correct.

=SUMPRODUCT(($B$7:$B$81>=1)*($B$7:$B$81<=25)*($D$7:$H$81=Q7)*($B$7:$B$81<>$D$7:$H$81))

 

[Carlos_m]

With:
Q7 = 1
B7 = 1

1. With this formula in cell R7 it does return 2.

=COUNTIF(INDIRECT("D7:L" & MATCH(TRUE,INDEX(ISBLANK($B$7:$B$496),0,0),0)+5),Q7)

Q7 (Number 1) R7 (Result) = 2

2. Tell me in plain speak what the 2 result in R7 is or represents. Why is it 2?
3. If B7 is blank, R column returns 0 (zero)

As for the "B" column "Numbers" are excluded.
What does this excluded statement mean. Excluded from what or where?
Howard

1) The code I supplied was as close as I have come - it is No Good.
2) The 2 result in R7 is the number of times #1 shows up in the range D7:L31
3) Yes - it should. IF B7 is blank, that should exclude ROW 7 from being counted.

What we are trying to do is to find how many times (In Total) a specific number occurs in columns "D", "E", "F", "G", "H", "I", "J", "K", and "L". But For This We are Only interested in the most recent 25 (Thus Column "B" numbering 1 to 25). The Numbers in column "B" have no part in the equation, other than to indicate that the numbers in "D", "E", "F", "G", "H", "I", "J", "K", and "L" of that ROW are to be counted.

The Numbers in ROW 7 to be counted are:
(D7)17, (E7)23, (F7)36, (G7)55, (H7)59, (I7)29, (J7)34, (K7)44, (L7)50

The Numbers in ROW 8 to be counted are:
(D8)14, (E8)18, (F8)28, (G8)29, (H8)57, (I8)24, (J8)43, (K8)44, (L8)45

With ONLY THOSE NUMBERS the results should be displayed in the table (Q1:X31)
(First number in "Number Column(Q,T,W)" and Second Number in "Occurrences" Column(R, U, X):
14=1 17=1, 18=1, 23=1, 24=1, 28=1, 29=2, 34=1, 36=1, 43=1, 44=2, 45=1, 50=1, 55=1, 57=1, 59=1

Does That Help Any?

 

[Carlos_m]

I assume that this should say D7:H81

Er - No, should actually be D7:L81 - But The Only Numbers that should be counted are in the D7:L51 Range (using ANY Number in column B as a qualifier in order to use that particular ROW.

Also, your occurrence counts in column R don't all match up with the information you have given us.

They should - if the results are obtained from the D7:L51 Range

=SUMPRODUCT(($B$7:$B$81>=1)*($B$7:$B$81<=25)*($D$7:$H$81=Q7)*($B$7:$B$81<>$D$7:$H$81))

Very nice try - finds 3/4 5's, 3/4 13's, 2/6 14's, 1/2 15's...

 

[L. Howard]

Does these four formulas work?

In cells R7, U7, X7 AA7 and pulled down to row 31.

Below the formula are a random sample of results.

=IF(B7<>"",COUNTIF($D$7:$L$31,Q7),"")

=IF(B7<>"",COUNTIF($D$7:$L$31,T7),"")

=IF(B7<>"",COUNTIF($D$7:$L$31,W7),"")

=IF(B7<>"",COUNTIF($D$7:$L$31,Z7),"")

10 1
11 4
12 1
13 5
14 6
15 2

45 5
46 6
47 6
48 5
49 7
50 5

57 3
58 4
59 6
60 0
61 0
62 0

95 0
96 0
97 0
98 0
99 0
100 0

Howard

 

[jasonb75]

The value in B7 (etc.) should be excluded from the results.

So B7 = 1, don't count any instance of 1 in D7:L7. B8 = 2, don't count any instance of 2 in D8:L8, etc?

That was how I read this and what the formula is based on, if that is incorrect then it will explain why some of the results are 1 less than they should be.

Er - No, should actually be D7:L81 - But The Only Numbers that should be counted are in the D7:L51 Range (using ANY Number in column B as a qualifier in order to use that particular ROW.

D7:L51 or D7:L31?

They should - if the results are obtained from the D7:L51 Range

I would disagree, looking at the numbers in D7:L31, 12 of your 19 sample results are incorrect, 13 if my assumption on the exclusion of B7 is correct.

Very nice try - finds 3/4 5's, 3/4 13's, 2/6 14's, 1/2 15's...

Following from my comment above, your sample specified 4 5's, 2 13's, 2 14's, 0 15's.

One of the 5's was excluded because it appears in the same row that has 5 in column B

The formula I provided you with will work when it is adjusted to the correct columns, although, if the exclusion of the5 above is incorrect then you will need to remove the last part of the formula and use

=SUMPRODUCT(($B$7:$B$81>=1)*($B$7:$B$81<=25)*($D$7:$L$81=Q7))