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

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> 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
>

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.


--
BCB
------------------------------------------------------------------------
BCB's Profile: http://www.excelforum.com/member.php...o&userid=34101
View this thread: http://www.excelforum.com/showthread...hreadid=540522

BCB:

Try this:

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

Does that help?

Regards,
Ro

--
Ron Coderr
-----------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...fo&userid=2141
View this thread: http://www.excelforum.com/showthread.php?threadid=54052

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

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

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"BCB" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> 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
>

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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> >
> > 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
> >
>
>
>

You're quite right: I have tried it on my most updated computer, and i
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'l
save me a lot of place and a good bit of work.
Thanks again.
BC

--
BC
-----------------------------------------------------------------------
BCB's Profile: http://www.excelforum.com/member.php...fo&userid=3410
View this thread: http://www.excelforum.com/showthread.php?threadid=54052