Excel IF Statement

Verb

Hi all;
I'm trying to write an IF Statement for a spreadsheet I'm setting up.

The sheet is made up of many lines of data. Each line has a series of single numbers (0-9). I want to know how many of each there is without having to go through each one.

Can anyone help??

TheMatrix_2

Set up 10 columns so as to test the number in the cell
e.g. if you are testing column A the first two columns B & C would be

=IF(A2=0,1,0)
=IF(A2=1,1,0)

Then at the bottom of the sheet sum up the columns

There may be a better way, but at least this will work

LucianH

It sounds like you want to use the COUNTIF function:

Syntax
COUNTIF(range, criteria)

The COUNTIF function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):
range Required. One or more cells to count, including numbers or names, arrays, or references that contain numbers. Blank and text values are ignored.
criteria Required. A number, expression, cell reference, or text string that defines which cells will be counted. For example, criteria can be expressed as 32, ">32", B4, "apples", or "32".

Notes
You can use the wildcard characters — the question mark (?) and the asterisk (*) — in criteria. A question mark matches any single character, and an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.
Criteria are case insensitive; for example, the string "apples" and the string "APPLES" will match the same cells.

googler

Yup, sounds like COUNTIF

Verb

All I want it to do is tell me how many 0's 1's 2's etc there is in any 1 line of data.

Can anyone help with the code for one??

LucianH

Lets say your you have a line of data in, say, cells A2 through F2. If you put a "0" in H1 (as a header) then in cell H2 you put:

=Countif($A2:$F2,H$1)

Then the cell will tell you how many 0's you have in that line.

Put "1" in I1 and put the following in I2:

=Countif($A2:$F2,I$1)

Will tell you how many 1's you have.

If your next line of data is in A3 thru F3 then put the following in H3 to count the 0's in that line:

=Countif($A3:$F3,H$1)

and so on

Does this help?

thescouselander

As the others have said - you need to use COUNTIF.


so to find out how many 0s there are:

=COUNTIF(range,0)

to count 1s it would be

=COUNTIF(range,1)

To get the range you can simply highlight the cells you want to check when you get to typing that bit.