Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Finding how many in a Excel spreadsheet

Posted on 2014-07-24
7
Medium Priority
?
123 Views
Last Modified: 2014-08-04
I have a spreadsheet with 90,000 rows and 10 columns. I need to find out how many of the cells contain a specific value. Let's say 538. How many cells have 538 their cell. I've tried to sort but the it's so unwieldy the program craps out. Sorting hangs and never recovers.

Any quick and simple method to extract this one bit of info?
0
Comment
Question by:xmouser
  • 3
  • 3
7 Comments
 
LVL 23

Accepted Solution

by:
NBVC earned 1200 total points
ID: 40217443
You can use COUNTIF

e.b.

=COUNTIF(A1:Z100,538)
0
 
LVL 13

Assisted Solution

by:duncanb7
duncanb7 earned 800 total points
ID: 40217495
NBVC is correct,  and  I modify this from him

=COUNTIF(A:Z,538)

That you don't need to consider the number of row  to be searched when
put the formula out of A:Z range , ie, put it at  Cell AA

Duncan
0
 

Author Comment

by:xmouser
ID: 40217597
=COUNT(F1:F99360, 538) gives me 99361 which is wrong, it give me 99361 no matter what value I'm looking for.

=COUNT(F1:F99360, 540) gives me 99362, etc.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 13

Expert Comment

by:duncanb7
ID: 40217603
it is COUNTIF not COUNT


Duncan
0
 
LVL 13

Expert Comment

by:duncanb7
ID: 40217611
Please take a look the Excel tutorial at this site if have time
http://www.techonthenet.com/excel/formulas/countif.php

Duncan
0
 

Author Comment

by:xmouser
ID: 40217926
Typo on my part.
0
 

Author Closing Comment

by:xmouser
ID: 40238852
Sorry for the delay I've been out for a week.
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

I came across an unsolved Outlook issue and here is my solution.
MS Outlook undoubtedly is the most widely used email client.Its user-friendliness, cost effectiveness, and availability with Microsoft Office Suite make it the most popular email application.  Its compatibility with Microsoft applications like Exch…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

581 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