We'd like to remind Forumites to please avoid political debate on the Forum... Read More »
The Forum is currently experiencing technical issues which the team are working to resolve. Thank you for your patience.
📨 Have you signed up to the Forum's new Email Digest yet? Get a selection of trending threads sent straight to your inbox daily, weekly or monthly!
Excel IF Statement

Verb
Posts: 227 Forumite


in Techie Stuff
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??
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??
0
Comments
-
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 work0 -
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.Never let it get you down... unless it really is as bad as it seems.0 -
Yup, sounds like COUNTIF0
-
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??0 -
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?Never let it get you down... unless it really is as bad as it seems.0 -
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.0
This discussion has been closed.
Confirm your email address to Create Threads and Reply

Categories
- All Categories
- 350.5K Banking & Borrowing
- 252.9K Reduce Debt & Boost Income
- 453.3K Spending & Discounts
- 243.4K Work, Benefits & Business
- 598.1K Mortgages, Homes & Bills
- 176.7K Life & Family
- 256.5K Travel & Transport
- 1.5M Hobbies & Leisure
- 16.1K Discuss & Feedback
- 37.6K Read-Only Boards