[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
Athalon
General User

Joined: 10 Feb 2006
Posts: 40
Location: Michigan, USA

 Posted: Fri Feb 10, 2006 3:26 pm    Post subject: Another "If" Question. (I'm learning) Okay- Two Rows. Each cell contains either a "True" or a "False". If two cells next to each other both have trues, I want it counted. =COUNTIF(M5:M782;"TRUE");(N5:N782,"TRUE"). This formula does not work. Please "Stear me straight". thanks.
David
Super User

Joined: 24 Oct 2003
Posts: 5668

Posted: Fri Feb 10, 2006 3:44 pm    Post subject: Re: Another "If" Question. (I'm learning)

 Athalon wrote: Okay- Two Rows. Each cell contains either a "True" or a "False". If two cells next to each other both have trues, I want it counted. =COUNTIF(M5:M782;"TRUE");(N5:N782,"TRUE"). This formula does not work. Please "Stear me straight". thanks.

In O5 put this: =IF(AND(M5=TRUE;N5=TRUE);1;0)
Copy down column O
Sum column O.

The TRUEs will change in the formula bar to 1s, but that's OK.

David.
RickRandom
Super User

Joined: 27 Jan 2006
Posts: 1082
Location: UK

 Posted: Fri Feb 10, 2006 3:48 pm    Post subject: The 2 step answer could be, with your rows of TRUE and FALSE in A1 to D4: 1. In A3, put: =AND(A1;A2) and fill across into B3, etc. [You could just use =A1*A2 because Calc treats TRUE as 1, FALSE as 0. 2. Put in A4: =COUNTIF(A3:D3;1) [You can put TRUE instead of 1, but it changes to 1.] I expect there are better ways, perhaps using SUMPRODUCT( ) if your TRUEs and FALSEs are in arrays, but I don't use arrays, so don't know the details. Let us know your success or failure, in the latter case someone will try again. Well I looked at SUMPRODUCT( ) and it worked! Use: =SUMPRODUCT(A1:D1;A2:D2) I get an answer of 2, as I've got TRUEs in C1, C2, D1, D2. So at least I've learnt something.
Athalon
General User

Joined: 10 Feb 2006
Posts: 40
Location: Michigan, USA

 Posted: Fri Feb 10, 2006 4:00 pm    Post subject: It Worked It Worked David. The idea is to get a whole row of 1 or 0 and then do a countif at the bottom. I was not thinking this way but it worked. Thanks a bunch. Until next time. Matt
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum