?
Solved

Number Count in Excel

Posted on 2011-04-26
14
Medium Priority
?
304 Views
Last Modified: 2012-06-27
Hi Experts,

Its been a long time since I asked a question here.

Anyway,  I was wondering if someone could help me figure out how to get a number count in Excel.

Let me explain. In the attached spreadsheet you will see a row of numbers. I would like to know how many times a particular number is => than a number in cell A2:A???? and have the answer in column W. For example, the number 163.57 is the number represented for IBM. That number has appeared twice as either = to 163.57 (cell c2) or greater than 163.57 (cell D2) and the answer is placed in W2.

Cheers

Carlton
EEBACKTEST.xlsm
0
Comment
Question by:cpatte7372
  • 7
  • 3
  • 3
  • +1
14 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35468247
=COUNTIF(C2:V2,">="&A2)
in W2 and copy down.
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 35468264
Try this formula in W2

=COUNTIF(B2:V2,">="&A2)

regards, barry
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35468270
Oh oh...
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:cpatte7372
ID: 35468659
Guys,

Thats great. It worked.

Can I add another request?

Is it possible for the spreadsheet to highlight where the cells were => or simply let me know somewhere in the spreadsheet what cells were =>

Its great knowing how many times, would be fantastic if it could tell me which cells were greater than or equal to.

Really appreciate it guys.

Cheer
0
 

Author Comment

by:cpatte7372
ID: 35468698
I was thinking that maybe somekind of conditional format would do the trick.

For example, highlight cell if => a number in cells A2:A?????

Would do you guys think?

Cheers
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35468716
You can use conditional formatting with a formula like (assuming you select B2:V whatever)
=B2>=$A2

Note the $ before the A.
0
 

Author Comment

by:cpatte7372
ID: 35468971
Hi Rorya,

Thanks for responding. Going to check it out and let you know how I got on.

Cheers
0
 

Author Comment

by:cpatte7372
ID: 35469099
rorya,

Shouldn't the formula be =C2>=$A2 ?

B2 doesn't have any numbers, just symbols.

Cheers
0
 

Author Comment

by:cpatte7372
ID: 35469133
I was thinking that there might be a conditional format which says if any of the cells C2:V2 are => A2 then highlight the individual cell.

Is that not possible?

Cheers
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 35469189
That's what Rory's suggestion does - select the whole range, as Rory says, and then apply the formula, for example select C2:V34 then use that formula in conditional formatting

=C2>=$A2

Excel automatically adjusts it so that it works correctly for all cells

regards, barry
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 2000 total points
ID: 35469241
I applied it to your worksheet

see attached

regards, barry
26978103.xlsm
0
 

Author Comment

by:cpatte7372
ID: 35469275
Thats fantastic,

Thanks chaps

Really appreciate you guys/girls on this Exchange.

Cheers
0
 

Author Closing Comment

by:cpatte7372
ID: 35704082
Brill
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35713763
Ahem.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question