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

  • Thread starter Thread starter BCB
  • Start date Start date
B

BCB

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

Try this:

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

Does that help?

Regards,
Ro
 
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
 
Try re-typing it, it works fine, and requires no add-ins.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)
 
If the columns are already 1's and 0's, why not just

=SUMPRODUCT(A2:A21,B2:B21)


Bob Phillips said:
Try re-typing it, it works fine, and requires no add-ins.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)
Click to expand...
 
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
 
Back
Top