Summing Boolean

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

 

[tompl]

Boolean means true or false. I think you would need to count trues or count
falses.

 

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

 

[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

 

[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

 

[Dave]

Hi Dana,
Thanks. That works.
But still puzzeled as to how we Sum a lot of cells in VBA
Regards - 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.

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
.

 

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

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
.

 

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

 

[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

 

[Dave]

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

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.

.