Excel formula help

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…

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

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. :bow:
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…
:cool:

How about the COUNTIFfunction?

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.

Ah yes…that’s even better.

Look’s like I don’t even need row limits.
=COUNTIF(A:F,7)

I know I’ll have to do this again soon, so thanks for this.
:slight_smile: