• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 309
  • Last Modified:

Number Count in Excel

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
cpatte7372
Asked:
cpatte7372
  • 7
  • 3
  • 3
  • +1
1 Solution
 
Rory ArchibaldCommented:
=COUNTIF(C2:V2,">="&A2)
in W2 and copy down.
0
 
barry houdiniCommented:
Try this formula in W2

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

regards, barry
0
 
zorvek (Kevin Jones)ConsultantCommented:
Oh oh...
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
cpatte7372Author Commented:
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
 
cpatte7372Author Commented:
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
 
Rory ArchibaldCommented:
You can use conditional formatting with a formula like (assuming you select B2:V whatever)
=B2>=$A2

Note the $ before the A.
0
 
cpatte7372Author Commented:
Hi Rorya,

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

Cheers
0
 
cpatte7372Author Commented:
rorya,

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

B2 doesn't have any numbers, just symbols.

Cheers
0
 
cpatte7372Author Commented:
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
 
barry houdiniCommented:
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
 
barry houdiniCommented:
I applied it to your worksheet

see attached

regards, barry
26978103.xlsm
0
 
cpatte7372Author Commented:
Thats fantastic,

Thanks chaps

Really appreciate you guys/girls on this Exchange.

Cheers
0
 
cpatte7372Author Commented:
Brill
0
 
Rory ArchibaldCommented:
Ahem.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now