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

C

Carlos_m

New Member
Joined
Oct 28, 2014
Messages
9
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...
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Sort by date Sort by votes
Hi Carlos_M,

Do you want to count the number of times the value in cell B7 occurs in the range D7:J31?

And return that value to cell R7?

And then the same for B8 and R8... etc?


If not, then using cell references show some correct return examples of what you want.

Howard
 
Upvote 0
L. Howard said:
Hi Carlos_M,
Do you want to count the number of times the value in cell B7 occurs in the range D7:J31?
And return that value to cell R7?
And then the same for B8 and R8... etc?

If not, then using cell references show some correct return examples of what you want.
Howard
Click to expand...

Hi Howard,
Thanks for responding...

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

Q3 (Number 1) R3 (Result) = 2
Q4 (Number 2) R4 (Result) = 2
Q5 (Number 3) R5 (Result) = 4
Q6 (Number 4) R6 (Result) = 4
Q7 (Number 5) R7 (Result) = 4
Q8 (Number 6) R8 (Result) = 4
Q9 (Number 7) R9 (Result) = 2
Q10 (Number 8) R10 (Result) = 3
Q11 (Number 9) R11 (Result) = 1
Q12 (Number 10) R12 (Result) = 1


About the "Closest" I've gotten so far is:
=COUNTIF(INDIRECT("D7:L" & MATCH(TRUE,INDEX(ISBLANK($B$3:$B$496),0,0),0)+5),Q3)

With two problems:
1) doesn't work if I sort Column "A" low to high.
2) Results are "Off"

Would also like to be able to pick top 25 and top 10 (code adjustment) - (Ideal - but not critical).
 
Upvote 0
Try thinking of it like trying to pick the most common numbers in the lottery (with a few extra balls)-
Using the most recent 10 and/or 25 results for the "Hottest" Numbers.

(This way would also explain the need for adding additional/future numbers)
 
Upvote 0
Completely lost, sorry.



Q3 is a blank cell right above the word "Number" on the sheet you posted.
R3 just above the word Occurrences.

B7 the number being excluded...??

Howard
 
Upvote 0
L. Howard said:
Completely lost, sorry.

Q3 is a blank cell right above the word "Number" on the sheet you posted.
R3 just above the word Occurrences.

B7 the number being excluded...??

Howard
Click to expand...

Er - sorry didn't mean to confuse things..

Yes - that is correct
 
Upvote 0
Q3 is a blank cell right above the word "Number" on the sheet you posted.
R3 just above the word Occurrences.

B7 the number being excluded...??
Click to expand...

Sorry - I miss read this. (Was looking at a copy of the file I was working on)
Yes - the "B" column "Numbers" are excluded.

The Copy You Have:

Q7 (Number 1) R7 (Result) = 2
Q8 (Number 2) R8 (Result) = 2
Q9 (Number 3) R9 (Result) = 4
Q10 (Number 4) R10 (Result) = 4
Q11 (Number 5) R11 (Result) = 4
Q12 (Number 6) R12 (Result) = 4
Q13 (Number 7) R13 (Result) = 2
Q14 (Number 8) R14 (Result) = 3
Q15 (Number 9) R15 (Result) = 1
Q16 (Number 10) R16 (Result) = 1

Hope that makes it clearer...
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question
VLOOKUP, COUNTIFS, SUMPRODUCT for Counting Specific Events Occurring During a Range of Dates?
Replies
1
Views
401
drshenanigans
D
M
  • Question
Excel - Identify number of business days for a specific month within a weekly date range
Replies
3
Views
488
duggie33
D
C
  • Question
Compare dates in two columns across several rows and count based on criteria.
Replies
2
Views
315
ccalkins
C
B
  • Solved
Search for matches and delete in multiple columns.
Replies
4
Views
347
Bradzo
B
O
  • Question
Count only one occurrence of all criteria being met
Replies
8
Views
408
KRice
K

Forum statistics

Threads
1,214,649
Messages
6,120,732
Members
448,987
Latest member
marion_davis

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top