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

1. ## 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. ## 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
>

3. 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.

4. BCB:

Try this:

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

Does that help?

Regards,
Ron

5. 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. ## 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
>

7. ## 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
> >

>
>
>

8. 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

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

#### 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