Excel formula help

What we recommend:

1. Read the submitted posts below for information and help from other users.
2. Before and after making any changes to your system or installing any software we strongly recommend you check your computer for Windows registry errors with Registry Booster 2010.

General Software Discuss, Excel formula help at Software forum; Hi, I have 6 very long columns of numbers. (A thru F) and 6000 rows. I need to find out how many times the number "7" appears. Just a total count of how many 7s are in the whole spreadsheet. I tried messing with the "count" function, but can't get

Posted: 09-02-2010

Aquatarkus95 (MyCE Rookie): Posts: 40

Hi,
I have 6 very long columns of numbers. (A thru F) and 6000 rows.
I need to find out how many times the number "7" appears.

Just a total count of how many 7s are in the whole spreadsheet.

I tried messing with the "count" function, but can't get it right.
Any help would be appreciated.

Thanks...

Quote and reply

Today (MyCE Staff): Posts: 15,596

Posted: 09-02-2010

midders (Batchshrink Author): Posts: 783

Personally, I'd import your spreadsheet into Access and use sql: select count(*) from tablethis where columthat=7 or columnthat2=7...

A quick fix to do it in excel: use if(A1=7,1,0) in column G, if(B1=7,1,0) in column H and so on upto column L, then drag the formulas over the 6000 rows and sum the results. i.e. sum(G1:L6000)

I'm a bit rusty so my syntax may need checking but that should give you the rough idea.

Slainte

midders

__________________
Author of BatchShrink a batch wrapper for DVD Shrink 3.2
Check out my batch command line tool for DivX conversion!
Hardware: Old, slow lappy with LG HL-DT-ST DVDRAM GSA-E10L

and new HTPC
Software: Windows XP Pro SP3, DVD Fab, DVDShrink, Virtualdub, ImgBurn, ProjectX, Mpeg2Schnitt, GUI for DVDAuthor
Thoughts: "I'd never belong to a club that would have me as a member" - Groucho Marx
"Some people bring happiness wherever they go, others whenever they go" - Oscar Wilde

Quote and reply

Posted: 09-02-2010

Aquatarkus95 (MyCE Rookie): Posts: 40

Thanks midders,

I'm not savvy with Access at all, so I went with the quick fix.
I didn't think to attempt an "if" statement.
That worked great.

I summed each column at the bottom, then final summed the last row.

It's interesting, after pasting down all the formulas, you can see where
each "7" is actually located. Like a big grid. (ha)

I appreciate the help...

Quote and reply

Posted: 09-02-2010

Da_Taxman (Senior (non-technical) Admin): Posts: 15,262

How about the COUNTIF function?

__________________

By registering to our forum you accepted our rules and policies (Dutch), so respect CD Freaks by respecting these rules and policies;
Please read these helpful tips and links for all (newbies and oldtimers)
Please describe your problem as clear as possible in your topic title, so that others can see what it is about and are more inclined to help if it is something they know something about;
I do not provide technical support over E-mail or Private Message (emails with such requests will be bounced);
Please post your questions as clear as possible, so others can help you get a correct answer sooner;
Please use our search, you might get an answer to your question sooner that way;
For any forum related issues you can contact me through PM;
I don't dislike newbies, just lazy people who expect others to do their work for them.

Quote and reply

Posted: 09-02-2010

DrageMester (Retired Moderator): Posts: 18,488

Let's say as an example that your numbers are in the range A2:F6001

You can then use the COUNTIF function as suggested by Da_Taxman to count all those cells that contain the number 7, a formula that results in the number 7, or a string that when evaluated as a number results in 7:

=COUNTIF(A2:F6001;7)

Depending on your locale (language) you may have to use comma instead of semicolon in the formula.

If you want to also count numbers that are the results of calculations and which end up very very close to 7 (e.g. 6.99999999999), but not exactly 7, then things become much more complicated.

__________________
Do not meddle in the affairs of dragons, for you are crunchy and taste good with ketchup.