We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Number Count in Excel

cpatte7372
cpatte7372 asked
on
Medium Priority
315 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
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
=COUNTIF(C2:V2,">="&A2)
in W2 and copy down.
CERTIFIED EXPERT
Most Valuable Expert 2013

Commented:
Try this formula in W2

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

regards, barry
CERTIFIED EXPERT
Top Expert 2008

Commented:
Oh oh...

Author

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

Author

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
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
You can use conditional formatting with a formula like (assuming you select B2:V whatever)
=B2>=$A2

Note the $ before the A.

Author

Commented:
Hi Rorya,

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

Cheers

Author

Commented:
rorya,

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

B2 doesn't have any numbers, just symbols.

Cheers

Author

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
CERTIFIED EXPERT
Most Valuable Expert 2013

Commented:
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
CERTIFIED EXPERT
Most Valuable Expert 2013
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Thats fantastic,

Thanks chaps

Really appreciate you guys/girls on this Exchange.

Cheers

Author

Commented:
Brill
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
Ahem.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.