+ Reply to Thread
Results 1 to 8 of 8

Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next

  1. #1
    Registered User
    Join Date
    05-03-2006
    Location
    Mellerud, Sweden
    MS-Off Ver
    2016
    Posts
    70

    Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next

    Hi

    I am new here and have been snooping around some of the threads, yet not quite found the simple formula I expect to solve my problem. The closest matches seemed to advanced for my need, so I reckon and hope this will be an easy one for the more experienced members.

    What I'm looking for is a formula that will:
    Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a column with similar conditions in the next column.
    Say A2:A21 contains 9 "0"s and the remaining 11 are "1"s, of which only 5 (the "1"s) appear on the same row as "1"s occuring in the next column, B (which is of the same length and with the "1"s and "0"s unevenly distributed downward).

    These five are the ones I'd like my Excel to count - in just one cell. The issue is avoiding further multiplying of columns.

    The basic SUMIF-function just doesn't count the ones. I've done some feeble tweaking of several formulas, but just gotten some VALUE!-exclamations back at best.
    The ExcelTutor-business with the fruit-trees of different heights and whatnot seemed to be the ticket, if one could simplify it some way - but then it does not work when copied to a sheet. Maybe a virus..

    Hope someone knows the trick, and thanks in advance

    B

  2. #2
    Peo Sjoblom
    Guest

    Re: Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next

    If they are numbers you can use


    =SUMPRODUCT(--(A2:A21=1),--(B2:B21=1))


    --

    Regards,

    Peo Sjoblom

    Excel 95 - Excel 2007
    Northwest Excel Solutions
    www.nwexcelsolutions.com
    "It is a good thing to follow the first law of holes;
    if you are in one stop digging." Lord Healey


    "BCB" <BCB.27klka_1147228503.3936@excelforum-nospam.com> wrote in message
    news:BCB.27klka_1147228503.3936@excelforum-nospam.com...
    >
    > Hi
    >
    > I am new here and have been snooping around some of the threads, yet
    > not quite found the simple formula I expect to solve my problem. The
    > closest matches seemed to advanced for my need, so I reckon and hope
    > this will be an easy one for the more experienced members.
    >
    > What I'm looking for is a formula that will:
    > Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a column with similar
    > conditions in the next column.
    > Say A2:A21 contains 9 "0"s and the remaining 11 are "1"s, of which only
    > 5 (the "1"s) appear on the same row as "1"s occuring in the next column,
    > B (which is of the same length and with the "1"s and "0"s unevenly
    > distributed downward).
    >
    > These five are the ones I'd like my Excel to count - in just one cell.
    > The issue is avoiding further multiplying of columns.
    >
    > The basic SUMIF-function just doesn't count the ones. I've done some
    > feeble tweaking of several formulas, but just gotten some
    > VALUE!-exclamations back at best.
    > The ExcelTutor-business with the fruit-trees of different heights and
    > whatnot seemed to be the ticket, if one could simplify it some way -
    > but then it does not work when copied to a sheet. Maybe a virus..
    >
    > Hope someone knows the trick, and thanks in advance
    >
    > B
    >
    >
    > --
    > BCB
    > ------------------------------------------------------------------------
    > BCB's Profile:
    > http://www.excelforum.com/member.php...o&userid=34101
    > View this thread: http://www.excelforum.com/showthread...hreadid=540522
    >




  3. #3
    Registered User
    Join Date
    05-03-2006
    Location
    Mellerud, Sweden
    MS-Off Ver
    2016
    Posts
    70
    Thanks anyway, Mr. Sjoblom - your formula might come in handy in some other parts of my workbooks, but the "1"s mentioned are the results of other formulas. There were errors related to "nesting" involved, according to some Excel explanation that popped up.
    I was hoping there might be a way to get around it (in one single cell), before I eventually spend an extra sheet on it.
    Appreciate your help, though.

  4. #4
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996
    BCB:

    Try this:

    C1: =SUMPRODUCT(--ISNUMBER(1/(A2:A21*B2:B21)))

    Does that help?

    Regards,
    Ron

  5. #5
    Registered User
    Join Date
    05-03-2006
    Location
    Mellerud, Sweden
    MS-Off Ver
    2016
    Posts
    70
    Thank you, Mr. Coderre.

    Tried it, but got #NAME? in return.
    I fear it might be required to install some of the accessories from the Tools menu, and that's not an option for me yet. Anyway, that was just one of several solutions suggested by Excel Help.
    Thanks anyway.
    B

  6. #6
    Bob Phillips
    Guest

    Re: Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next

    Try re-typing it, it works fine, and requires no add-ins.

    --
    HTH

    Bob Phillips

    (remove xxx from email address if mailing direct)

    "BCB" <BCB.27mj0a_1147318502.1146@excelforum-nospam.com> wrote in message
    news:BCB.27mj0a_1147318502.1146@excelforum-nospam.com...
    >
    > Thank you, Mr. Coderre.
    >
    > Tried it, but got #NAME? in return.
    > I fear it might be required to install some of the accessories from the
    > Tools menu, and that's not an option for me yet. Anyway, that was just
    > one of several solutions suggested by Excel Help.
    > Thanks anyway.
    > B
    >
    >
    > --
    > BCB
    > ------------------------------------------------------------------------
    > BCB's Profile:

    http://www.excelforum.com/member.php...o&userid=34101
    > View this thread: http://www.excelforum.com/showthread...hreadid=540522
    >




  7. #7
    JMB
    Guest

    Re: Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. con

    If the columns are already 1's and 0's, why not just

    =SUMPRODUCT(A2:A21,B2:B21)


    "Bob Phillips" wrote:

    > Try re-typing it, it works fine, and requires no add-ins.
    >
    > --
    > HTH
    >
    > Bob Phillips
    >
    > (remove xxx from email address if mailing direct)
    >
    > "BCB" <BCB.27mj0a_1147318502.1146@excelforum-nospam.com> wrote in message
    > news:BCB.27mj0a_1147318502.1146@excelforum-nospam.com...
    > >
    > > Thank you, Mr. Coderre.
    > >
    > > Tried it, but got #NAME? in return.
    > > I fear it might be required to install some of the accessories from the
    > > Tools menu, and that's not an option for me yet. Anyway, that was just
    > > one of several solutions suggested by Excel Help.
    > > Thanks anyway.
    > > B
    > >
    > >
    > > --
    > > BCB
    > > ------------------------------------------------------------------------
    > > BCB's Profile:

    > http://www.excelforum.com/member.php...o&userid=34101
    > > View this thread: http://www.excelforum.com/showthread...hreadid=540522
    > >

    >
    >
    >


  8. #8
    Registered User
    Join Date
    05-03-2006
    Location
    Mellerud, Sweden
    MS-Off Ver
    2016
    Posts
    70
    You're quite right: I have tried it on my most updated computer, and it works perfectly. Thank you all!
    Now I have some updating to do (I hope it's just a language problem), before I can continue my work, hopefully with the new tools. They'll save me a lot of place and a good bit of work.
    Thanks again.
    BCB

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1