Summing "False" and "True"
 

I have an equality -

Cell A1 "=B1C1"
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.

Try this:

=SUMPRODUCT(--(B1:B100C1:C100))

--

HTH,

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

"Jeff" wrote in message
...
I have an equality -

Cell A1 "=B1C1"
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.

Jeff wrote:
I have an equality -

Cell A1 "=B1C1"
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

Jeff wrote:
I have an equality -

Cell A1 "=B1C1"
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

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.

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

wrote in message
oups.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.

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.

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


wrote in message
ups.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.