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
Verb Posts: 227 Forumite
Part of the Furniture 100 Posts Combo Breaker
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??

Comments

  • TheMatrix_2
    TheMatrix_2 Posts: 113 Forumite
    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
    LucianH Posts: 445 Forumite
    Part of the Furniture 100 Posts Name Dropper Photogenic
    edited 5 June 2011 at 1:09PM
    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.
  • googler
    googler Posts: 16,103 Forumite
    Part of the Furniture 10,000 Posts Name Dropper Combo Breaker
    Yup, sounds like COUNTIF
  • Verb
    Verb Posts: 227 Forumite
    Part of the Furniture 100 Posts Combo Breaker
    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
    LucianH Posts: 445 Forumite
    Part of the Furniture 100 Posts Name Dropper Photogenic
    edited 5 June 2011 at 2:20PM
    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.
  • thescouselander
    thescouselander Posts: 5,547 Forumite
    Part of the Furniture 1,000 Posts Name Dropper Combo Breaker
    edited 5 June 2011 at 2:19PM
    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.
This discussion has been closed.
Meet your Ambassadors

🚀 Getting Started

Hi new member!

Our Getting Started Guide will help you get the most out of the Forum

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

Is this how you want to be seen?

We see you are using a default avatar. It takes only a few seconds to pick a picture.