Summing Boolean

D

Dave

Hi,
XL2003
I would like to Sum a named range of cells containing only True or False or
blank.
I tried:
Application.WorksheetFunction.Sum(Range("MyRange"))
But this gave zero, even if 'True' was present in the range.
And BTW, is there no SUM function in VBA?
I think I can use AVERAGEA, then multiply by COUNTA(MyRange), but that seems
a bit extreme.
Regards - Dave.
 
G

Gary''s Student

The conversion is killing you. Put TRUE in A1 and FALSE in A2 and run this:

Sub dural()
MsgBox CInt(Range("A1").Value)
MsgBox CInt(Range("A2").Value)
MsgBox CInt(Range("A1").Value) + CInt(Range("A2").Value)
End Sub

to see what I mean. What is True on the worksheet may not be True in VBA.
 
D

Dana DeLouis

Hi,
XL2003
I would like to Sum a named range of cells containing only True or False or
blank.
I tried:
Application.WorksheetFunction.Sum(Range("MyRange"))
But this gave zero, even if 'True' was present in the range.
And BTW, is there no SUM function in VBA?
I think I can use AVERAGEA, then multiply by COUNTA(MyRange), but that seems
a bit extreme.
Regards - Dave.


Sounds like you want to "Count"

One of a few examples...

Sub Demo()
Debug.Print WorksheetFunction.CountIf(Range("MyRange"), True)
'or..
Debug.Print WorksheetFunction.CountIf([MyRange], False)
Debug.Print WorksheetFunction.CountIf([MyRange], "")
End Sub

= = = = = = =
HTH :>)
Dana DeLouis
 
D

Dave

Hi GS,
Thanks.
So 'True' in VBA equates to -1. OK.
I also found that:
CInt(Range("A1").Value) + CInt(Range("A2").Value)
and
Range("A1") + Range("A2")
both returned -1.
So how do we SUM in VBA when there's lots of cells?

Regards - Dave
 
D

Dave

Hi Dana,
Thanks. That works.
But still puzzeled as to how we Sum a lot of cells in VBA
Regards - Dave.

Dana DeLouis said:
Hi,
XL2003
I would like to Sum a named range of cells containing only True or False or
blank.
I tried:
Application.WorksheetFunction.Sum(Range("MyRange"))
But this gave zero, even if 'True' was present in the range.
And BTW, is there no SUM function in VBA?
I think I can use AVERAGEA, then multiply by COUNTA(MyRange), but that seems
a bit extreme.
Regards - Dave.


Sounds like you want to "Count"

One of a few examples...

Sub Demo()
Debug.Print WorksheetFunction.CountIf(Range("MyRange"), True)
'or..
Debug.Print WorksheetFunction.CountIf([MyRange], False)
Debug.Print WorksheetFunction.CountIf([MyRange], "")
End Sub

= = = = = = =
HTH :>)
Dana DeLouis
.
 
D

Dana DeLouis

But still puzzeled as to how we Sum a lot of cells in VBA

Hi. Here's one way...

Debug.Print WorksheetFunction.Sum(Range("A1:A10"))

HTH
Dana DeLouis


Hi Dana,
Thanks. That works.
But still puzzeled as to how we Sum a lot of cells in VBA
Regards - Dave.

Dana DeLouis said:
Hi,
XL2003
I would like to Sum a named range of cells containing only True or False or
blank.
I tried:
Application.WorksheetFunction.Sum(Range("MyRange"))
But this gave zero, even if 'True' was present in the range.
And BTW, is there no SUM function in VBA?
I think I can use AVERAGEA, then multiply by COUNTA(MyRange), but that seems
a bit extreme.
Regards - Dave.


Sounds like you want to "Count"

One of a few examples...

Sub Demo()
Debug.Print WorksheetFunction.CountIf(Range("MyRange"), True)
'or..
Debug.Print WorksheetFunction.CountIf([MyRange], False)
Debug.Print WorksheetFunction.CountIf([MyRange], "")
End Sub

= = = = = = =
HTH :>)
Dana DeLouis
.
 
D

Dave

Hi Dana,
But when we have boolean entries, Range("A1")+Range("A2")+Range("A3") works,
summing False's as 0's, and True's as -1's.
But WorksheetFunction.Sum(Range("A1:A10")) does not seem to work with
boolean entries, always returning 0.

Dave.
 
D

Dana DeLouis

But WorksheetFunction.Sum(Range("A1:A10")) does not seem to work with
boolean entries, always returning 0.

Hi. Summing 'Boolean' entries doesn't make much sense compared to
"Counting."
However, to do so, I believe you need to first convert the boolean to a
value so that 'Sum can work.

Here's one way to Sum 'True as the value 1.

Sub Demo()
Dim x
x = WorksheetFunction.SumProduct([--MyRange])
End Sub

Just note that on a Worksheet, True is +1
I still think "Count" is what you want though :>)

= = = = = = =
HTH :>)
Dana DeLouis
 
D

Dave

Hi Dana,
OK, I concede. Count it is.
Dave.

Dana DeLouis said:
But WorksheetFunction.Sum(Range("A1:A10")) does not seem to work with
boolean entries, always returning 0.

Hi. Summing 'Boolean' entries doesn't make much sense compared to
"Counting."
However, to do so, I believe you need to first convert the boolean to a
value so that 'Sum can work.

Here's one way to Sum 'True as the value 1.

Sub Demo()
Dim x
x = WorksheetFunction.SumProduct([--MyRange])
End Sub

Just note that on a Worksheet, True is +1
I still think "Count" is what you want though :>)

= = = = = = =
HTH :>)
Dana DeLouis





Hi Dana,
But when we have boolean entries, Range("A1")+Range("A2")+Range("A3") works,
summing False's as 0's, and True's as -1's.
But WorksheetFunction.Sum(Range("A1:A10")) does not seem to work with
boolean entries, always returning 0.

Dave.


.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top