Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old May 10th 06, 03:31 AM posted to microsoft.public.excel.newusers
BCB
 
Posts: n/a
Default 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


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


  #2   Report Post  
Old May 10th 06, 04:59 AM posted to microsoft.public.excel.newusers
Peo Sjoblom
 
Posts: n/a
Default 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" wrote in message
...

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   Report Post  
Old May 11th 06, 12:05 AM posted to microsoft.public.excel.newusers
BCB
 
Posts: n/a
Default Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next


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

  #4   Report Post  
Old May 11th 06, 12:51 AM posted to microsoft.public.excel.newusers
Ron Coderre
 
Posts: n/a
Default Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next


BCB:

Try this:

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

Does that help?

Regards,
Ron


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

  #5   Report Post  
Old May 11th 06, 04:34 AM posted to microsoft.public.excel.newusers
BCB
 
Posts: n/a
Default Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next


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



  #6   Report Post  
Old May 11th 06, 12:36 PM posted to microsoft.public.excel.newusers
Bob Phillips
 
Posts: n/a
Default 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" wrote in message
...

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   Report Post  
Old May 12th 06, 05:58 AM posted to microsoft.public.excel.newusers
JMB
 
Posts: n/a
Default 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" wrote in message
...

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   Report Post  
Old May 13th 06, 10:02 PM posted to microsoft.public.excel.newusers
BCB
 
Posts: n/a
Default Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next


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


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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Count using 2 conditions, one of which being a "less than or equal to" - URGENT SamGB Excel Discussion (Misc queries) 2 February 15th 06 11:35 AM
COUNT using multiple conditions SamGB Excel Discussion (Misc queries) 2 February 9th 06 11:12 PM
Trying to count occurences if 2 conditions are met NLithgow Excel Worksheet Functions 3 June 8th 05 07:20 AM
Count number of occurences in 1 column only if something in anothe Wenster Excel Worksheet Functions 2 February 7th 05 10:58 PM
Count occurences between dates DJ Dusty Excel Worksheet Functions 3 November 11th 04 01:25 AM


All times are GMT +1. The time now is 03:07 AM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017