Summing "False" and "True"

I have an equality - 

Cell A1 "=B1>C1"
The value is true since cell B1=10 and C1=3

This repeats down to row 100. A2, A3, ...A100

I want to sum up the number of True statements.  I use 
Sumproduct((A1:A100=1)*1),

But this equals zero.  Why doesn't this add 1 when the value is true.  Isn't 
the value of cell A1 = 1 since the result is a true statement.


0
Jeff1 (635)
8/24/2005 3:11:06 PM
excel.misc 78881 articles. 5 followers. Follow

7 Replies
469 Views

Similar Articles

[PageSpeed] 6

Try this:

=SUMPRODUCT(--(B1:B100>C1:C100))

-- 

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

"Jeff" <Jeff@discussions.microsoft.com> wrote in message
news:919C625F-3DA5-4334-BACE-A07B64205D8B@microsoft.com...
I have an equality -

Cell A1 "=B1>C1"
The value is true since cell B1=10 and C1=3

This repeats down to row 100. A2, A3, ...A100

I want to sum up the number of True statements.  I use
Sumproduct((A1:A100=1)*1),

But this equals zero.  Why doesn't this add 1 when the value is true.  Isn't
the value of cell A1 = 1 since the result is a true statement.



0
ragdyer1 (4060)
8/24/2005 3:26:25 PM
Jeff wrote:
> I have an equality - 
> 
> Cell A1 "=B1>C1"
> The value is true since cell B1=10 and C1=3
> 
> This repeats down to row 100. A2, A3, ...A100
> 
> I want to sum up the number of True statements.  I use 
> Sumproduct((A1:A100=1)*1),
> 
> But this equals zero.  Why doesn't this add 1 when the value is true.  Isn't 
> the value of cell A1 = 1 since the result is a true statement.
> 
> 

One approach which works is:

   ={SUM(IF(D4:D100=TRUE,1,0))}

Note that this is an array formula, so when you've typed it in you have to hit 
Shift-Ctrl-Enter.

Bill
0
wylie836 (251)
8/24/2005 5:04:20 PM
Jeff wrote:
> I have an equality - 
> 
> Cell A1 "=B1>C1"
> The value is true since cell B1=10 and C1=3
> 
> This repeats down to row 100. A2, A3, ...A100
> 
> I want to sum up the number of True statements.  I use 
> Sumproduct((A1:A100=1)*1),
> 
> But this equals zero.  Why doesn't this add 1 when the value is true.  Isn't 
> the value of cell A1 = 1 since the result is a true statement.
> 
> 

One approach which works is:

   ={SUM(IF(D4:D100=TRUE,1,0))}

Note that this is an array formula, so when you've typed it in you have to hit
Shift-Ctrl-Enter.

A simpler approach is:

   =COUNTIF(D1:D100,TRUE)


Bill
0
wylie836 (251)
8/24/2005 5:06:39 PM
Jeff wrote:
> I want to sum up the number of True statements.  I use
> Sumproduct((A1:A100=1)*1),
> But this equals zero.

SUMPRODUCT(1*(A1:A100)) seems to work.

0
joeu2004 (766)
8/24/2005 8:26:26 PM
You could simply use:

=SUMPRODUCT(--(A1:A100))

But this still necessitates using ColumnA as a sort of "helper" column, when
it's really superfluous.
-- 
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

<joeu2004@hotmail.com> wrote in message
news:1124915186.889023.232520@g44g2000cwa.googlegroups.com...
> Jeff wrote:
> > I want to sum up the number of True statements.  I use
> > Sumproduct((A1:A100=1)*1),
> > But this equals zero.
>
> SUMPRODUCT(1*(A1:A100)) seems to work.
>

0
ragdyer1 (4060)
8/24/2005 8:47:04 PM
<joeu2004@hotmail.com> wrote:
> Jeff wrote:
> > I want to sum up the number of True statements.
> > I use Sumproduct((A1:A100=1)*1),
>
> SUMPRODUCT(1*(A1:A100)) seems to work.

RagDyer wrote:
> You could simply use:
> =SUMPRODUCT(--(A1:A100))
>
> But this still necessitates using ColumnA as a
> sort of "helper" column, when it's really superfluous.

And perhaps the OP is interested in that.  But since the
OP already indicated an interest in having "helper" cells
in column A, I thought it was prudent to keep them in the
solution.

(Perhaps they serve some other purpose in the OP's application.)

As for "1*" v. "--", first, I think "1*" is more intuitive
than the double-negative.  More to the point, it is what
the OP tried to do in the first place; ergo, it is probably
more intuitive to the OP as well.  I thought it was
instructive to show the OP how to correct his mistake rather
than "throw the baby out with the bath water" unnecessarily.

To each his own.  I was purposely trying to demonstrate a
different approach than those already presented.

0
joeu2004 (766)
8/24/2005 11:38:36 PM
You're absolutely correct in your contention of following an OP's lead as
being a good route for suggesting solutions.

It's my contention that perhaps he didn't realize that it could be done in a
more concise manner.

And BTW,
I personally *hate* the unary.
But in some *rare* instances, it just "looks" neater, as in single argument
situations.<g>
-- 
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------


<joeu2004@hotmail.com> wrote in message
news:1124926716.040669.57690@o13g2000cwo.googlegroups.com...
> <joeu2004@hotmail.com> wrote:
> > Jeff wrote:
> > > I want to sum up the number of True statements.
> > > I use Sumproduct((A1:A100=1)*1),
> >
> > SUMPRODUCT(1*(A1:A100)) seems to work.
>
> RagDyer wrote:
> > You could simply use:
> > =SUMPRODUCT(--(A1:A100))
> >
> > But this still necessitates using ColumnA as a
> > sort of "helper" column, when it's really superfluous.
>
> And perhaps the OP is interested in that.  But since the
> OP already indicated an interest in having "helper" cells
> in column A, I thought it was prudent to keep them in the
> solution.
>
> (Perhaps they serve some other purpose in the OP's application.)
>
> As for "1*" v. "--", first, I think "1*" is more intuitive
> than the double-negative.  More to the point, it is what
> the OP tried to do in the first place; ergo, it is probably
> more intuitive to the OP as well.  I thought it was
> instructive to show the OP how to correct his mistake rather
> than "throw the baby out with the bath water" unnecessarily.
>
> To each his own.  I was purposely trying to demonstrate a
> different approach than those already presented.
>

0
ragdyer1 (4060)
8/25/2005 2:50:15 AM
Reply:

Similar Artilces:

counting TRUEs and FALSEs
A large range of cells (6 columns by 400 rows) all contain an OR-function formula and all display a TRUE or FALSE value. (Results based on processing of a nearby range of data.) I want to count how many TRUEs occur in each row, and display the result for each row in the 7th column. I'm having trouble figuring this one out, since all the functions I've looked at so far apparently see only the formula and not the value. =countif(a1:f1,TRUE) should work. mitcheroo wrote: > > A large range of cells (6 columns by 400 rows) all contain an > OR-function formula and all display...

IF for inverting true/false
hello all !! i have this.... =OR(AND(T65-U65>=Q55*N11;L10=TRUE);(U55*Q56<=T55)) but for some formal reason i need it to give false when it's true and vice versa.. with this... if i am not wrong... =IF(OR(AND(T65-U65>=Q55*N11;L10=TRUE);(U55*Q56<=T55));FALSE;TRUE) am i right ?? tx !! paolo Just use the NOT function on your original expression... =NOT(OR(AND(T65-U65>=Q55*N11;L10=TRUE);(U55*Q56<=T55))) -- Rick (MVP - Excel) "pls123" <pls123@discussions.microsoft.com> wrote in message news:0E13B7A5-A512-4A55-A3EA-AA25515A77C8@mi...

How do I show a checkbox in a cell holding a true/false or yes/no or 1/0 value?
I'm sure I've seen this in Excel spreadsheets before, but can't find an example anywhere and I'm getting frustrated with the help system's insistence on showing the answers to completely irrelvant questions I just didn't ask. I want the spreadsheet user to be able to check or uncheck a box to signify a yes/no value. Should be simple right? Somebody help me out, please. Brian Lowe ---------@ You could try drawing check boxes from the Forms Toolbar, via View > Toolbars > Forms (not from the Control Toolbox) See : http://tinyurl.com/vykc for a previous outline...

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

function for true/false on a range of cells
I have a range of cells with text that I want to get a true/false calculation. IF function only takes 1 cell at a time, or I am at a loss to how to put in an addtional function to see the range then calculate. Any ideas? If you input a range into the IF function, it will return an array (fill a range) if you array enter it (Ctrl-Shift-Enter). Jerry ewil wrote: > I have a range of cells with text that I want to get a true/false > calculation. IF function only takes 1 cell at a time, or I am at a > loss to how to put in an addtional function to see the range then > calcula...

Reverse True/False
Do I have to just live with it? A table has a field named "Inactive". That's counter-intuitive is this case. I would like to change the field name to "Active" and update the data accordingly. That is, make True become False and vice-versa. All my query attempts end in frustration. It is a Y/N field...I don't need nulls. Is there a simple technique? Can someone advise the simple way OR tell me to just get over it? -- Thanks for your help, Chrissy Chris You can do an update query that turns the current value of the field into the Not([ThatField]), reversi...

how: if, conditional test, true (do nothing if false)
I want a simply if then without the else. =if(Condtion,true response,"") will give a blank for a false response, is this what you mean? "jwhitney" wrote: > I want a simply if then without the else. We have daily and weekly (and monthly and biannual) checks, I want the date on the weekly to update (dd/mm/yy) on "Mon" but not change the rest of the week. "bj" wrote: > =if(Condtion,true response,"") > will give a blank for a false response, > is this what you mean? > > "jwhitney" wrote: > > > I want a...

problem with true/false statements
Hi there trying to do a formula based on true/false output but I cant get round this Team A(B1) v(D1)Team B IF SUM(B1>D1) say 1 - 0 {if true = 1 if false = 0} IF SUM(B1<D1) say 0 - 1 {if true = 1 if false = 0} No problem threre.. BUT IF SUM (B1=D1) say 0 -0 or 1 -1 {if true = 1 if false =0}OK but the trouble is if blank in both cells still shows 1 I need it to show 0 false basically if b1=" " and D1=" " then false = 0 Please help I can't solve it - pulling my hair out what little I have left. Cheers Paul > IF SUM(B1>D1) say 1 - 0 {if true = 1 if...

How to change the return of a textbox from TRUE/FALSE to YES/NO?
I have a long colum full of textboxes in excel. When the boc is checked it returns true, and false for unchecked. I would like to change this so that it returns Yes for checked and No for unchecked. How can I make this happen You mean Checkboxes? You can use a helper cell: =if(a1=true,"Yes","No") Then hide that linkedcell (if you want). Gldenboy2 wrote: > > I have a long colum full of textboxes in excel. When the boc is checked it > returns true, and false for unchecked. I would like to change this so that > it returns Yes for checked and No for un...

If True then copy and paste, If False do nothing
I have a form Clients based on a table Prospects and I have fields Client address, Client City, Client State, Client Zip on my form and in my table. I want to put a check box for Billing address is the same as Client Address and if the box is checked yes I want the data form this client copied from the above fields to Client Billing address, Client Billing City, Client Billing State, & Client Billing Zip. I have no clue how to do this can someone Helllllpppp. thanks Gary In the BeforeInsert event of the form, check the value of the checkbox and if it's True, copy the values...

True/False, On/Off
Hello, I have a cell (say A1) which contains a value 0 or 1 , or equivalently FALSE or TRUE. I use it in an IF function elsewhere,, eg. IF(A1,'Cat','Dog'). No problem so far. I would prefer to display 'OFF' or 'ON' in A1. Can I make OFF & ON equivalent to boolean values, or must I change the formula to IF(a1='ON', 'Cat','Dog') ? Thanks K The second option is needed IF(A1="ON", "Cat", "Dog") Note the double quotes (I expect you meant to use them!) There is no way to...

Want to export "True" or "False" not 1 or 0 to tet file.
I am exporting a table in Access 2003 to a text file which is imported in to a third party appplication. However, there are a few colums with "True" and "False" filelds but when access generates the text file it replaces true and false with 1 and 0 I need it to export "True" or "False" as the third party application does not accept 1 or 0 Is there any way to achieve this. Thanks You would have to export from a query and use a calculated field. Field: SomeFieldTF: IIF(SomeYesNoField,"True","False") Or as ...

What's the best way to toggle between true and false in Excel? Hi all, My excel work involves a lot of toggling between true and false (boolean types) ... and it's very repetitive... Is there a w
What's the best way to toggle between true and false in Excel? Hi all, My excel work involves a lot of toggling between true and false (boolean types) ... and it's very repetitive... Is there a way to select a bunch of cells, and press a key short-cut so that they toggle all at once? Thanks! I can't tell you whether this is a "best way" to do what you want or not, but it is "a way" to do it. It doesn't use a key short-cut, rather it uses a right mouse click. Go into the VB editor and double click on ThisWorkbook in the Project window, then copy/paste...

replacing TRUE/FALSE
Is what I am trying to do possible. I have a column of data that is just showing either TRUE or FALSE Using =IF(E2=TRUE,"Yes","No") I can change another column to show yes or no. What I need to do is automatically select all in Column E except the first row then apply the formula. Once complete replace the values into the original cells in Column E. I tried Range("E65536").End(xlUp).Offset(1, 0).Select in my macro but it didn't seem to do what I wanted Is this all possible? -- Rogueuk Hi if you don't need to do this via code, i would use this app...

True or False
Is it true that excel can't calculate (16,51 - 16) What should be the result of: =IF((16,51-16)=((1651-1600)/100);"TRUE";"FALSE") Thank you for your attention Excel (and all computers) work in binary. 16.51-16 yields 0.510000000000002 and (1651-1600)/100 yields 0.51, so they're different. This kind of thing isn't new. If you need this kind of calculation, use either precision as displayed or the ROUND function. "gr8posts" <gr8posts@discussions.microsoft.com> wrote in message news:CB0F10FC-8105-42E6-BEB5-5FB04A5BC709@microsoft.com... >...

True/false statement help
Hi all just after a quick fix (and probably a very simple one too, lol) I want a cell to display 0 if a different cell is less than a given value (in this case 95). One of the issues i face is that the cell i want to display as 0 (in the above instance) is needing a calculation in there. b4 = the cell which will determine the outcome, less than 95=0 in cell b10 b5 = value b9 = value b10 = b5+b9 currently (but should display 0 if b4 is less than 95) All help appreciated :) yY Did you look in the help index for IF? in b10 =if(b4<95,0,b5+b9) -- Don Guillett Microsoft MVP Excel SalesAid...

IF statement based on True/False that activates a certain list box
I've been asked to create a formula that will react to either "yes" or "no" entered into a cell which will show options in another cell based on a certain list. Example: Cells D3:D5 contain the values Monday, Tuesday, Wednesday (list named BegWeek) Cells E3:E5 contain the values Thursday, Friday, Saturday (list named EndWeek) Cell A3 accepts either "yes" or "no" entry only from a list If A3 = "Yes" then B3 will = drop down list BegWeek If A3 = "No" then B3 will = drop down list End Week I don't even know if thi...

True/False: Class 'CSomething' indicates descended from CObject
I'll Try one more time: Naming a class 'CSomething' indicates that it is a descendant of 'CObject'. This means that it is, among other things, serializable, and that it provides run-time type identification. If you look up the help in visual studio for 'COBJECT' you will see these definitions. So, if you write your own classes, and they are NOT descendants of CObject, they should NOT be named 'CANYTHING'! Any opinions? Steve Schilz I3nospamSAS@aoldotcom "I3SAS" <i3sas@aol.com> wrote in message news:20040107175537.21823.00001406@mb-m19...

Need "true" or "false"
Hi All, I need the column f2 to show whether it is true or false..... Below is the scenario.... A1 B1 C1 D1 E1 F1(result) s s s s s s s y s y I need the formula for F1 (result) so that from column A:E, if it is all "s", the result should be "true", if column A:E, even i column has y, the result should be "false" Thanks in advance!!! Vicky Hi Vicky Look at this: =IF(COUNTIF(A1:E1,"=s")=5,TRUE,FALSE) Regards, Per "Vicky&qu...

text color change if value is true, another color if value is false
I am using a DAYS360 formula and I want the result to be returned in green font if the value is less than or equal to 21 days, orange font if the value is equal to or less than 28 days and red font if the result is greater than or equal to 28 days Any suggestions? Have a look at conditional formatting. -- Don Guillett SalesAid Software donaldb@281.com "Michelle Sheldon" <anonymous@discussions.microsoft.com> wrote in message news:5E621598-064C-4157-8F88-11F9AD2155F2@microsoft.com... > I am using a DAYS360 formula and I want the result to be returned in green font if the ...

How to return a number instead of true/false with an if function?
Please help =IF(your condition,1,2) eg =IF(A1=B1,1,2) will return a 1 if TRUE, and a 2 if FALSE -- HTH Kassie Replace xxx with hotmail "charles" wrote: > Please help Always elaborate on your issue in the message area (that big white space). The subject line's just that, meant to be brief keynotes on your issue. It's NOT your query. Elaborate by posting your formula or your formula attempts and by describing details of what you have (your sheet set-up, sample data) and what you want to do/happen Anyway, here's 2 simple IF examples to groove you in its use ...

Displaying data based on a TRUE/FALSE value in a cell
I have 3 sheets with peoples names and several test scores for each person on it. (A3:O3 would be one persons data persay.) Each person has a checkbox next to it to identify if they are eligible for awards. This check box turns the value of cell A1 to TRUE if checked and FALSE if unchecked. I want a sheet that will show all of the peoples names from each of the three sheets that has a FALSE value in column A. I only need columns B, C, and O displayed on the new page. If at all possible can make them automatically show in order from the highest to lowest Value in column O? :confused: -- s...

TRUE/ FALSE in check boxes do not toggle
I created a form in Excel 2007 that uses calculations. The calculations in the form work great on my home computer (WINDOWS 7, Excel 2007), BUT the calculations do not calculate at work using the same file. At work we have Excel 2007 and XP on a network. I've tried it on one of my co-worker's computer, no luck. I sent the same file to a friend who uses XP and Excel 2003 and it works just fine. The check boxes do not toggle from TRUE to False. but stay on FALSE even though the box is checked. As a result. the calculations do not compute. Why just on my pc at work? ...

True or False Formula?
Hi Everyone. I've just got myself Excel and I'm trying to create a simple spreadsheet. What I need to do is have C calculated automatically. I have searched Google for a hint, but I don't know Excel that well enough yet plus English is my second language. A1>B1=C1 If A is greater than B, C is the answer. 1>5=False 1>0=True 1>1=Equal or 0 from what I read Very much thanks to anyone who can help learn this. Joanne Ramirez Hello. I have worked out part of my probelm and realized I need help with more. I am trying to learn this. =(A1>B1)OR(A1<B1)OR(A1=B1) ...

if/true/false help
I know this is probably simple, but i just can't get it to work :o I have a column of numbers in a worksheet. I get updated data feeds that may have changes in the numbers. I copy this column next to the existing column. I need a formula that will tell me if each row in the column matches the row in the next column. so E2 is true if it matches F2, E3 to F3, E4 to F4, etc and false if it does not match. -- Thanks :) Lori Paste this formula in G2 =IF(AND(E2="",F2=""),"",E2=F2) Copy the G2 formula and paste it to the remaining cells of...