SUMPRODUCT troubles

[Luke]

I've looked through the various posts concerning this, but still can't quite
get this to work. I have a ranges named ClaimClients and ClaimStat and a
value named ClientNum. I want to find the number of times the ClaimClients
range has a value of ClientNum and the ClaimStat range has a value of "O".
Here is the code at present:

ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT((" & _
ClaimClients & "=""" & ClientNum & """),(" & ClaimStat & "=""O""))")

With all the various things I've tried to make this work, I always get
either a Type Mismatch error or a Syntax error.

 

[Joe User]

I've looked through the various posts concerning this, but still can't
quite
get this to work. I have a ranges named ClaimClients and ClaimStat and a
value named ClientNum. I want to find the number of times the
ClaimClients
range has a value of ClientNum and the ClaimStat range has a value of "O".
Here is the code at present:

ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT((" & _
ClaimClients & "=""" & ClientNum & """),(" & ClaimStat &
"=""O""))")

With all the various things I've tried to make this work, I always get
either a Type Mismatch error or a Syntax error.

 

[Joe User]

I have a ranges named ClaimClients and ClaimStat and a
value named ClientNum.

[Sorry about the premature posting. Hit the wrong "button".]

Are those named Excel objects, or are they the names of VBA variables?

If they are VBA variables, what are their definitions and how are their
values assigned?

Presumably:

Dim ClaimClients as Range, ClaimStat as Range
Set ClaimClients = Range("a1:a10")
Set ClaimStat = Range("b1:b10")

If that is the case, then you should use ClaimClients.Address and
ClaimStat.Address in the Evaluate string. Failure to do so would cause a
Type Mismatch error.

But you might have other errors, which, at the very least, might cause the
Evaluate function to fail to deliver the intended result.

It is unclear what type ClientNum is and how its value was assigned.

Your code snippet treats the value of ClientNum as a string. But is that
truly your intent? Is that the type of values in the cells represented by
the range variable ClaimClients?

Also, at a minimum, you need "--" before each SUMPRODUCT parameter.

In summary, the following might be what you intended:

ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT(--(" & _
ClaimClients & "=""" & ClientNum & """),--(" & ClaimStat & "=""O""))")

FYI, that can also be written:

ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT((" & _
ClaimClients & "=""" & ClientNum & """)*(" & ClaimStat & "=""O""))")

However, if ClaimClients, ClaimStat and pehaps even ClientNum are named
Excel objects, you should write:

ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT((" & _
"ClaimClients=ClientNum)*(ClaimStat=""O""))")

Or if ClientNum is a VBA variable:

ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT((" & _
"ClaimClients=""" & ClientNum & """)*(ClaimStat=""O""))")

PS: You could write simply Evaluate instead of ActiveSheet.Evaluate.

If none of those comments help, please post more VBA context in your
response, and address the questions I raised above.


----- original message -----

 

[Luke]

Rats, it's still not working. To answer your questions:

1) ClaimClients and ClaimStat are both Dim'd as ranges and assigned using
"Set", as you assumed. The ClaimClients range contains 4-digit numbers and
the ClaimStat range contains O's and C's.
2) ClientNum is a VBA defined variable (Dim'd as Integer).

All those quotation marks cross my eyes. If you can help me get them in the
right spot I'll sure appreciate it.

I have a ranges named ClaimClients and ClaimStat and a
value named ClientNum.

[Sorry about the premature posting. Hit the wrong "button".]

Are those named Excel objects, or are they the names of VBA variables?

If they are VBA variables, what are their definitions and how are their
values assigned?

Presumably:

Dim ClaimClients as Range, ClaimStat as Range
Set ClaimClients = Range("a1:a10")
Set ClaimStat = Range("b1:b10")

If that is the case, then you should use ClaimClients.Address and
ClaimStat.Address in the Evaluate string. Failure to do so would cause a
Type Mismatch error.

But you might have other errors, which, at the very least, might cause the
Evaluate function to fail to deliver the intended result.

It is unclear what type ClientNum is and how its value was assigned.

Your code snippet treats the value of ClientNum as a string. But is that
truly your intent? Is that the type of values in the cells represented by
the range variable ClaimClients?

Also, at a minimum, you need "--" before each SUMPRODUCT parameter.

In summary, the following might be what you intended:

ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT(--(" & _
ClaimClients & "=""" & ClientNum & """),--(" & ClaimStat & "=""O""))")

FYI, that can also be written:

ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT((" & _
ClaimClients & "=""" & ClientNum & """)*(" & ClaimStat & "=""O""))")

However, if ClaimClients, ClaimStat and pehaps even ClientNum are named
Excel objects, you should write:

ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT((" & _
"ClaimClients=ClientNum)*(ClaimStat=""O""))")

Or if ClientNum is a VBA variable:

ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT((" & _
"ClaimClients=""" & ClientNum & """)*(ClaimStat=""O""))")

PS: You could write simply Evaluate instead of ActiveSheet.Evaluate.

If none of those comments help, please post more VBA context in your
response, and address the questions I raised above.


----- original message -----

I've looked through the various posts concerning this, but still can't
quite
get this to work. I have a ranges named ClaimClients and ClaimStat and a
value named ClientNum. I want to find the number of times the
ClaimClients
range has a value of ClientNum and the ClaimStat range has a value of "O".
Here is the code at present:

ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT((" & _
ClaimClients & "=""" & ClientNum & """),(" & ClaimStat &
"=""O""))")

With all the various things I've tried to make this work, I always get
either a Type Mismatch error or a Syntax error.

.

 

[Joe User]

Rats, it's still not working.

Exactly what does not work? Please post the altered statement(s) that you
tried. I offered several to try.

1) ClaimClients and ClaimStat are both Dim'd as ranges and assigned using
"Set", as you assumed.

Okay, then you should use ClaimClients.Address and ClaimStat.Address, as I
mentioned before.

The ClaimClients range contains 4-digit numbers

When people write such things, I become very suspicious. Does ClaimClients
contain numbers per se, perhaps with the Custom format "0000" without
quotes? Or does ClaimClients contain 4-character strings of digits?

I presume you mean the latter. But even if you do as well, it would behoove
you use the temporary formula =TYPE(ClaimClients), copied parallel to the
entire range, to be sure that all of the range is the expected type, be it
numeric (1) or text (2).

2) ClientNum is a VBA defined variable (Dim'd as Integer).

Then simply writing ClientNum in the VBA expression does not ensure that you
get a 4-character string. Note that the string "0012" is not equal to the
string "12".

Try the following:

ActiveCell.Offset(0, 10).Value = _
ActiveSheet.Evaluate("SUMPRODUCT((" & _
ClaimClients.Address & "=""" & Format(ClientNum, "0000") & _
""")*(" & ClaimStat.Address & "=""O""))")

All those quotation marks cross my eyes. If you can help me
get them in the right spot I'll sure appreciate it.

Me, too. Copy-and-paste from this message to the VBA editing pane.


----- original message -----

Rats, it's still not working. To answer your questions:

1) ClaimClients and ClaimStat are both Dim'd as ranges and assigned using
"Set", as you assumed. The ClaimClients range contains 4-digit numbers
and
the ClaimStat range contains O's and C's.
2) ClientNum is a VBA defined variable (Dim'd as Integer).

All those quotation marks cross my eyes. If you can help me get them in
the
right spot I'll sure appreciate it.

I have a ranges named ClaimClients and ClaimStat and a
value named ClientNum.

[Sorry about the premature posting. Hit the wrong "button".]

Are those named Excel objects, or are they the names of VBA variables?

If they are VBA variables, what are their definitions and how are their
values assigned?

Presumably:

Dim ClaimClients as Range, ClaimStat as Range
Set ClaimClients = Range("a1:a10")
Set ClaimStat = Range("b1:b10")

If that is the case, then you should use ClaimClients.Address and
ClaimStat.Address in the Evaluate string. Failure to do so would cause a
Type Mismatch error.

But you might have other errors, which, at the very least, might cause
the
Evaluate function to fail to deliver the intended result.

It is unclear what type ClientNum is and how its value was assigned.

Your code snippet treats the value of ClientNum as a string. But is that
truly your intent? Is that the type of values in the cells represented
by
the range variable ClaimClients?

Also, at a minimum, you need "--" before each SUMPRODUCT parameter.

In summary, the following might be what you intended:

ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT(--(" &
_
ClaimClients & "=""" & ClientNum & """),--(" & ClaimStat & "=""O""))")

FYI, that can also be written:

ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT((" & _
ClaimClients & "=""" & ClientNum & """)*(" & ClaimStat & "=""O""))")

However, if ClaimClients, ClaimStat and pehaps even ClientNum are named
Excel objects, you should write:

ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT((" & _
"ClaimClients=ClientNum)*(ClaimStat=""O""))")

Or if ClientNum is a VBA variable:

ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT((" & _
"ClaimClients=""" & ClientNum & """)*(ClaimStat=""O""))")

PS: You could write simply Evaluate instead of ActiveSheet.Evaluate.

If none of those comments help, please post more VBA context in your
response, and address the questions I raised above.


----- original message -----

I've looked through the various posts concerning this, but still can't
quite
get this to work. I have a ranges named ClaimClients and ClaimStat and
a
value named ClientNum. I want to find the number of times the
ClaimClients
range has a value of ClientNum and the ClaimStat range has a value of
"O".
Here is the code at present:

ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT((" &
_
ClaimClients & "=""" & ClientNum & """),(" & ClaimStat &
"=""O""))")

With all the various things I've tried to make this work, I always get
either a Type Mismatch error or a Syntax error.

.

 

[Luke]

AH, THANK YOU!

The only thing I changed was just leaving ClientNum as is instead of using
Format, since all values of ClientNum are greater than 1000 and, as such,
would not have any leading 0's. So, really, all that was necessary was to
add ".Address" to each of my ranges. Just to further my education, why is
that necessary here (i.e., with SUMPRODUCT) and not in other places where I
refer to those ranges?

Many, many thanks.

Rats, it's still not working.

Exactly what does not work? Please post the altered statement(s) that you
tried. I offered several to try.

1) ClaimClients and ClaimStat are both Dim'd as ranges and assigned using
"Set", as you assumed.

Okay, then you should use ClaimClients.Address and ClaimStat.Address, as I
mentioned before.

The ClaimClients range contains 4-digit numbers

When people write such things, I become very suspicious. Does ClaimClients
contain numbers per se, perhaps with the Custom format "0000" without
quotes? Or does ClaimClients contain 4-character strings of digits?

I presume you mean the latter. But even if you do as well, it would behoove
you use the temporary formula =TYPE(ClaimClients), copied parallel to the
entire range, to be sure that all of the range is the expected type, be it
numeric (1) or text (2).

2) ClientNum is a VBA defined variable (Dim'd as Integer).

Then simply writing ClientNum in the VBA expression does not ensure that you
get a 4-character string. Note that the string "0012" is not equal to the
string "12".

Try the following:

ActiveCell.Offset(0, 10).Value = _
ActiveSheet.Evaluate("SUMPRODUCT((" & _
ClaimClients.Address & "=""" & Format(ClientNum, "0000") & _
""")*(" & ClaimStat.Address & "=""O""))")

All those quotation marks cross my eyes. If you can help me
get them in the right spot I'll sure appreciate it.

Me, too. Copy-and-paste from this message to the VBA editing pane.


----- original message -----

Rats, it's still not working. To answer your questions:

1) ClaimClients and ClaimStat are both Dim'd as ranges and assigned using
"Set", as you assumed. The ClaimClients range contains 4-digit numbers
and
the ClaimStat range contains O's and C's.
2) ClientNum is a VBA defined variable (Dim'd as Integer).

All those quotation marks cross my eyes. If you can help me get them in
the
right spot I'll sure appreciate it.

I have a ranges named ClaimClients and ClaimStat and a
value named ClientNum.

[Sorry about the premature posting. Hit the wrong "button".]

Are those named Excel objects, or are they the names of VBA variables?

If they are VBA variables, what are their definitions and how are their
values assigned?

Presumably:

Dim ClaimClients as Range, ClaimStat as Range
Set ClaimClients = Range("a1:a10")
Set ClaimStat = Range("b1:b10")

If that is the case, then you should use ClaimClients.Address and
ClaimStat.Address in the Evaluate string. Failure to do so would cause a
Type Mismatch error.

But you might have other errors, which, at the very least, might cause
the
Evaluate function to fail to deliver the intended result.

It is unclear what type ClientNum is and how its value was assigned.

Your code snippet treats the value of ClientNum as a string. But is that
truly your intent? Is that the type of values in the cells represented
by
the range variable ClaimClients?

Also, at a minimum, you need "--" before each SUMPRODUCT parameter.

In summary, the following might be what you intended:

ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT(--(" &
_
ClaimClients & "=""" & ClientNum & """),--(" & ClaimStat & "=""O""))")

FYI, that can also be written:

ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT((" & _
ClaimClients & "=""" & ClientNum & """)*(" & ClaimStat & "=""O""))")

However, if ClaimClients, ClaimStat and pehaps even ClientNum are named
Excel objects, you should write:

ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT((" & _
"ClaimClients=ClientNum)*(ClaimStat=""O""))")

Or if ClientNum is a VBA variable:

ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT((" & _
"ClaimClients=""" & ClientNum & """)*(ClaimStat=""O""))")

PS: You could write simply Evaluate instead of ActiveSheet.Evaluate.

If none of those comments help, please post more VBA context in your
response, and address the questions I raised above.


----- original message -----

I've looked through the various posts concerning this, but still can't
quite
get this to work. I have a ranges named ClaimClients and ClaimStat and
a
value named ClientNum. I want to find the number of times the
ClaimClients
range has a value of ClientNum and the ClaimStat range has a value of
"O".
Here is the code at present:

ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT((" &
_
ClaimClients & "=""" & ClientNum & """),(" & ClaimStat &
"=""O""))")

With all the various things I've tried to make this work, I always get
either a Type Mismatch error or a Syntax error.

.

.

 

[Luke]

One more question, if you don't mind. What if I need to add one more test to
the sumproduct function? Can you give me an idea how to structure the
quotes? Say, for example, I also needed to check whether range ClaimRec
equalled "Y". Can you give me an idea how that would look?

I changed all my other 2-condition sumproducts in my code and they work fine
(thank you), but placing the quotes just right is giving me fits when trying
to add a third condition.

Rats, it's still not working.

Exactly what does not work? Please post the altered statement(s) that you
tried. I offered several to try.

1) ClaimClients and ClaimStat are both Dim'd as ranges and assigned using
"Set", as you assumed.

Okay, then you should use ClaimClients.Address and ClaimStat.Address, as I
mentioned before.

The ClaimClients range contains 4-digit numbers

When people write such things, I become very suspicious. Does ClaimClients
contain numbers per se, perhaps with the Custom format "0000" without
quotes? Or does ClaimClients contain 4-character strings of digits?

I presume you mean the latter. But even if you do as well, it would behoove
you use the temporary formula =TYPE(ClaimClients), copied parallel to the
entire range, to be sure that all of the range is the expected type, be it
numeric (1) or text (2).

2) ClientNum is a VBA defined variable (Dim'd as Integer).

Then simply writing ClientNum in the VBA expression does not ensure that you
get a 4-character string. Note that the string "0012" is not equal to the
string "12".

Try the following:

ActiveCell.Offset(0, 10).Value = _
ActiveSheet.Evaluate("SUMPRODUCT((" & _
ClaimClients.Address & "=""" & Format(ClientNum, "0000") & _
""")*(" & ClaimStat.Address & "=""O""))")

All those quotation marks cross my eyes. If you can help me
get them in the right spot I'll sure appreciate it.

Me, too. Copy-and-paste from this message to the VBA editing pane.


----- original message -----

Rats, it's still not working. To answer your questions:

1) ClaimClients and ClaimStat are both Dim'd as ranges and assigned using
"Set", as you assumed. The ClaimClients range contains 4-digit numbers
and
the ClaimStat range contains O's and C's.
2) ClientNum is a VBA defined variable (Dim'd as Integer).

All those quotation marks cross my eyes. If you can help me get them in
the
right spot I'll sure appreciate it.

I have a ranges named ClaimClients and ClaimStat and a
value named ClientNum.

[Sorry about the premature posting. Hit the wrong "button".]

Are those named Excel objects, or are they the names of VBA variables?

If they are VBA variables, what are their definitions and how are their
values assigned?

Presumably:

Dim ClaimClients as Range, ClaimStat as Range
Set ClaimClients = Range("a1:a10")
Set ClaimStat = Range("b1:b10")

If that is the case, then you should use ClaimClients.Address and
ClaimStat.Address in the Evaluate string. Failure to do so would cause a
Type Mismatch error.

But you might have other errors, which, at the very least, might cause
the
Evaluate function to fail to deliver the intended result.

It is unclear what type ClientNum is and how its value was assigned.

Your code snippet treats the value of ClientNum as a string. But is that
truly your intent? Is that the type of values in the cells represented
by
the range variable ClaimClients?

Also, at a minimum, you need "--" before each SUMPRODUCT parameter.

In summary, the following might be what you intended:

ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT(--(" &
_
ClaimClients & "=""" & ClientNum & """),--(" & ClaimStat & "=""O""))")

FYI, that can also be written:

ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT((" & _
ClaimClients & "=""" & ClientNum & """)*(" & ClaimStat & "=""O""))")

However, if ClaimClients, ClaimStat and pehaps even ClientNum are named
Excel objects, you should write:

ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT((" & _
"ClaimClients=ClientNum)*(ClaimStat=""O""))")

Or if ClientNum is a VBA variable:

ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT((" & _
"ClaimClients=""" & ClientNum & """)*(ClaimStat=""O""))")

PS: You could write simply Evaluate instead of ActiveSheet.Evaluate.

If none of those comments help, please post more VBA context in your
response, and address the questions I raised above.


----- original message -----

I've looked through the various posts concerning this, but still can't
quite
get this to work. I have a ranges named ClaimClients and ClaimStat and
a
value named ClientNum. I want to find the number of times the
ClaimClients
range has a value of ClientNum and the ClaimStat range has a value of
"O".
Here is the code at present:

ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT((" &
_
ClaimClients & "=""" & ClientNum & """),(" & ClaimStat &
"=""O""))")

With all the various things I've tried to make this work, I always get
either a Type Mismatch error or a Syntax error.

.

.

 

[Joe User]

AH, THANK YOU!
The only thing I changed was just leaving ClientNum as is instead
of using Format, since all values of ClientNum are greater than 1000

You're welcome. Glad it worked for you. I forgot to mention what you need
to do if ClaimClient contains actual numbers, not strings. But apparently
my presumption was right. So there is no need to complicate things further.

So, really, all that was necessary was to

add ".Address" to each of my ranges.
[...] why is that necessary here

Because you are trying to construct a string of the form
"SUMPRODUCT(($A$1:$A$100="1234")*...)". ClaimClient is a Range object in
VBA. The property ClaimClient.Address is the string "$A$1:$A$100".

not in other places where I refer to those ranges?

That depends on the context.

In the context WorksheetFunction.Sumproduct(ClaimClient,...), you are pass
the Range object, not the address range, to Sumproduct. Likewise in the
context Set ClaimClient=Range("a1:a100").

In the context Range("x1:x100")=ClaimClient, you are implicitly refering to
the .Value property of both range objects, left and right of the assignment
operator.


----- original message -----

AH, THANK YOU!

The only thing I changed was just leaving ClientNum as is instead of using
Format, since all values of ClientNum are greater than 1000 and, as such,
would not have any leading 0's. So, really, all that was necessary was to
add ".Address" to each of my ranges. Just to further my education, why is
that necessary here (i.e., with SUMPRODUCT) and not in other places where
I
refer to those ranges?

Many, many thanks.

Rats, it's still not working.

Exactly what does not work? Please post the altered statement(s) that
you
tried. I offered several to try.

1) ClaimClients and ClaimStat are both Dim'd as ranges and assigned
using
"Set", as you assumed.

Okay, then you should use ClaimClients.Address and ClaimStat.Address, as
I
mentioned before.

The ClaimClients range contains 4-digit numbers

When people write such things, I become very suspicious. Does
ClaimClients
contain numbers per se, perhaps with the Custom format "0000" without
quotes? Or does ClaimClients contain 4-character strings of digits?

I presume you mean the latter. But even if you do as well, it would
behoove
you use the temporary formula =TYPE(ClaimClients), copied parallel to the
entire range, to be sure that all of the range is the expected type, be
it
numeric (1) or text (2).

2) ClientNum is a VBA defined variable (Dim'd as Integer).

Then simply writing ClientNum in the VBA expression does not ensure that
you
get a 4-character string. Note that the string "0012" is not equal to
the
string "12".

Try the following:

ActiveCell.Offset(0, 10).Value = _
ActiveSheet.Evaluate("SUMPRODUCT((" & _
ClaimClients.Address & "=""" & Format(ClientNum, "0000") & _
""")*(" & ClaimStat.Address & "=""O""))")

All those quotation marks cross my eyes. If you can help me
get them in the right spot I'll sure appreciate it.

Me, too. Copy-and-paste from this message to the VBA editing pane.


----- original message -----

Rats, it's still not working. To answer your questions:

1) ClaimClients and ClaimStat are both Dim'd as ranges and assigned
using
"Set", as you assumed. The ClaimClients range contains 4-digit numbers
and
the ClaimStat range contains O's and C's.
2) ClientNum is a VBA defined variable (Dim'd as Integer).

All those quotation marks cross my eyes. If you can help me get them
in
the
right spot I'll sure appreciate it.

:

I have a ranges named ClaimClients and ClaimStat and a
value named ClientNum.

[Sorry about the premature posting. Hit the wrong "button".]

Are those named Excel objects, or are they the names of VBA variables?

If they are VBA variables, what are their definitions and how are
their
values assigned?

Presumably:

Dim ClaimClients as Range, ClaimStat as Range
Set ClaimClients = Range("a1:a10")
Set ClaimStat = Range("b1:b10")

If that is the case, then you should use ClaimClients.Address and
ClaimStat.Address in the Evaluate string. Failure to do so would
cause a
Type Mismatch error.

But you might have other errors, which, at the very least, might cause
the
Evaluate function to fail to deliver the intended result.

It is unclear what type ClientNum is and how its value was assigned.

Your code snippet treats the value of ClientNum as a string. But is
that
truly your intent? Is that the type of values in the cells
represented
by
the range variable ClaimClients?

Also, at a minimum, you need "--" before each SUMPRODUCT parameter.

In summary, the following might be what you intended:

ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT(--("
&
_
ClaimClients & "=""" & ClientNum & """),--(" & ClaimStat &
"=""O""))")

FYI, that can also be written:

ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT((" &
_
ClaimClients & "=""" & ClientNum & """)*(" & ClaimStat &
"=""O""))")

However, if ClaimClients, ClaimStat and pehaps even ClientNum are
named
Excel objects, you should write:

ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT((" &
_
"ClaimClients=ClientNum)*(ClaimStat=""O""))")

Or if ClientNum is a VBA variable:

ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT((" &
_
"ClaimClients=""" & ClientNum & """)*(ClaimStat=""O""))")

PS: You could write simply Evaluate instead of ActiveSheet.Evaluate.

If none of those comments help, please post more VBA context in your
response, and address the questions I raised above.


----- original message -----

I've looked through the various posts concerning this, but still
can't
quite
get this to work. I have a ranges named ClaimClients and ClaimStat
and
a
value named ClientNum. I want to find the number of times the
ClaimClients
range has a value of ClientNum and the ClaimStat range has a value
of
"O".
Here is the code at present:

ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT(("
&
_
ClaimClients & "=""" & ClientNum & """),(" & ClaimStat &
"=""O""))")

With all the various things I've tried to make this work, I always
get
either a Type Mismatch error or a Syntax error.

.

.

 

[Joe User]

I also needed to check whether range ClaimRec
equalled "Y". Can you give me an idea how that would look?

When constructing formulas as strings in VBA, I find it works best to write
the Excel formula -- and even to test it first using Excel. Then edit it
the formula to meet the VBA syntax requirements, depending on the context.
As you said, the number of adjacent double-quotes can be confusing.

So you might write the following Excel formula:

=SUMPRODUCT((ClaimClients=ClaimNum)*(ClaimStat="O")*(ClaimRec="Y"))

Now add the requisite double-quotes and ampersands to satisfy VBA,
converting any double-quotes above to a pair of double-quotes in VBA.

Evaluate("SUMPRODUCT((" & _
ClaimClients.Address & "=" & ClaimNum & ")*(" & _
ClaimStat & "=""O"")*(" & ClaimRec.Address & "=""Y""))")

BTW, another way to write the original Excel formula is:

=SUMPRODUCT((ClaimClients=ClaimNum)*(ClaimStat&ClaimRec="OY"))

The VBA syntax becomes:

Evaluate("SUMPRODUCT((" & _
ClaimClients.Address & "=" & ClaimNum & ")*(" _
ClaimStat.Address & "&" & ClaimRec.Address & "=""OY""))")


----- original message -----

One more question, if you don't mind. What if I need to add one more test
to
the sumproduct function? Can you give me an idea how to structure the
quotes? Say, for example, I also needed to check whether range ClaimRec
equalled "Y". Can you give me an idea how that would look?

I changed all my other 2-condition sumproducts in my code and they work
fine
(thank you), but placing the quotes just right is giving me fits when
trying
to add a third condition.

Rats, it's still not working.

Exactly what does not work? Please post the altered statement(s) that
you
tried. I offered several to try.

1) ClaimClients and ClaimStat are both Dim'd as ranges and assigned
using
"Set", as you assumed.

Okay, then you should use ClaimClients.Address and ClaimStat.Address, as
I
mentioned before.

The ClaimClients range contains 4-digit numbers

When people write such things, I become very suspicious. Does
ClaimClients
contain numbers per se, perhaps with the Custom format "0000" without
quotes? Or does ClaimClients contain 4-character strings of digits?

I presume you mean the latter. But even if you do as well, it would
behoove
you use the temporary formula =TYPE(ClaimClients), copied parallel to the
entire range, to be sure that all of the range is the expected type, be
it
numeric (1) or text (2).

2) ClientNum is a VBA defined variable (Dim'd as Integer).

Then simply writing ClientNum in the VBA expression does not ensure that
you
get a 4-character string. Note that the string "0012" is not equal to
the
string "12".

Try the following:

ActiveCell.Offset(0, 10).Value = _
ActiveSheet.Evaluate("SUMPRODUCT((" & _
ClaimClients.Address & "=""" & Format(ClientNum, "0000") & _
""")*(" & ClaimStat.Address & "=""O""))")

All those quotation marks cross my eyes. If you can help me
get them in the right spot I'll sure appreciate it.

Me, too. Copy-and-paste from this message to the VBA editing pane.


----- original message -----

Rats, it's still not working. To answer your questions:

1) ClaimClients and ClaimStat are both Dim'd as ranges and assigned
using
"Set", as you assumed. The ClaimClients range contains 4-digit numbers
and
the ClaimStat range contains O's and C's.
2) ClientNum is a VBA defined variable (Dim'd as Integer).

All those quotation marks cross my eyes. If you can help me get them
in
the
right spot I'll sure appreciate it.

:

I have a ranges named ClaimClients and ClaimStat and a
value named ClientNum.

[Sorry about the premature posting. Hit the wrong "button".]

Are those named Excel objects, or are they the names of VBA variables?

If they are VBA variables, what are their definitions and how are
their
values assigned?

Presumably:

Dim ClaimClients as Range, ClaimStat as Range
Set ClaimClients = Range("a1:a10")
Set ClaimStat = Range("b1:b10")

If that is the case, then you should use ClaimClients.Address and
ClaimStat.Address in the Evaluate string. Failure to do so would
cause a
Type Mismatch error.

But you might have other errors, which, at the very least, might cause
the
Evaluate function to fail to deliver the intended result.

It is unclear what type ClientNum is and how its value was assigned.

Your code snippet treats the value of ClientNum as a string. But is
that
truly your intent? Is that the type of values in the cells
represented
by
the range variable ClaimClients?

Also, at a minimum, you need "--" before each SUMPRODUCT parameter.

In summary, the following might be what you intended:

ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT(--("
&
_
ClaimClients & "=""" & ClientNum & """),--(" & ClaimStat &
"=""O""))")

FYI, that can also be written:

ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT((" &
_
ClaimClients & "=""" & ClientNum & """)*(" & ClaimStat &
"=""O""))")

However, if ClaimClients, ClaimStat and pehaps even ClientNum are
named
Excel objects, you should write:

ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT((" &
_
"ClaimClients=ClientNum)*(ClaimStat=""O""))")

Or if ClientNum is a VBA variable:

ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT((" &
_
"ClaimClients=""" & ClientNum & """)*(ClaimStat=""O""))")

PS: You could write simply Evaluate instead of ActiveSheet.Evaluate.

If none of those comments help, please post more VBA context in your
response, and address the questions I raised above.


----- original message -----

I've looked through the various posts concerning this, but still
can't
quite
get this to work. I have a ranges named ClaimClients and ClaimStat
and
a
value named ClientNum. I want to find the number of times the
ClaimClients
range has a value of ClientNum and the ClaimStat range has a value
of
"O".
Here is the code at present:

ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT(("
&
_
ClaimClients & "=""" & ClientNum & """),(" & ClaimStat &
"=""O""))")

With all the various things I've tried to make this work, I always
get
either a Type Mismatch error or a Syntax error.

.

.

 

[Joe User]

PS....

BTW, another way to write the original Excel formula is:
=SUMPRODUCT((ClaimClients=ClaimNum)*(ClaimStat&ClaimRec="OY"))

But I wouldn't recommend it(!). Not surprisingly, that form is slower than
the other form.


----- original message -----

I also needed to check whether range ClaimRec
equalled "Y". Can you give me an idea how that would look?

When constructing formulas as strings in VBA, I find it works best to
write the Excel formula -- and even to test it first using Excel. Then
edit it the formula to meet the VBA syntax requirements, depending on the
context. As you said, the number of adjacent double-quotes can be
confusing.

So you might write the following Excel formula:

=SUMPRODUCT((ClaimClients=ClaimNum)*(ClaimStat="O")*(ClaimRec="Y"))

Now add the requisite double-quotes and ampersands to satisfy VBA,
converting any double-quotes above to a pair of double-quotes in VBA.

Evaluate("SUMPRODUCT((" & _
ClaimClients.Address & "=" & ClaimNum & ")*(" & _
ClaimStat & "=""O"")*(" & ClaimRec.Address & "=""Y""))")

BTW, another way to write the original Excel formula is:

=SUMPRODUCT((ClaimClients=ClaimNum)*(ClaimStat&ClaimRec="OY"))

The VBA syntax becomes:

Evaluate("SUMPRODUCT((" & _
ClaimClients.Address & "=" & ClaimNum & ")*(" _
ClaimStat.Address & "&" & ClaimRec.Address & "=""OY""))")


----- original message -----

One more question, if you don't mind. What if I need to add one more
test to
the sumproduct function? Can you give me an idea how to structure the
quotes? Say, for example, I also needed to check whether range ClaimRec
equalled "Y". Can you give me an idea how that would look?

I changed all my other 2-condition sumproducts in my code and they work
fine
(thank you), but placing the quotes just right is giving me fits when
trying
to add a third condition.

Rats, it's still not working.

Exactly what does not work? Please post the altered statement(s) that
you
tried. I offered several to try.


1) ClaimClients and ClaimStat are both Dim'd as ranges and assigned
using
"Set", as you assumed.

Okay, then you should use ClaimClients.Address and ClaimStat.Address, as
I
mentioned before.


The ClaimClients range contains 4-digit numbers

When people write such things, I become very suspicious. Does
ClaimClients
contain numbers per se, perhaps with the Custom format "0000" without
quotes? Or does ClaimClients contain 4-character strings of digits?

I presume you mean the latter. But even if you do as well, it would
behoove
you use the temporary formula =TYPE(ClaimClients), copied parallel to
the
entire range, to be sure that all of the range is the expected type, be
it
numeric (1) or text (2).


2) ClientNum is a VBA defined variable (Dim'd as Integer).

Then simply writing ClientNum in the VBA expression does not ensure that
you
get a 4-character string. Note that the string "0012" is not equal to
the
string "12".

Try the following:

ActiveCell.Offset(0, 10).Value = _
ActiveSheet.Evaluate("SUMPRODUCT((" & _
ClaimClients.Address & "=""" & Format(ClientNum, "0000") & _
""")*(" & ClaimStat.Address & "=""O""))")


All those quotation marks cross my eyes. If you can help me
get them in the right spot I'll sure appreciate it.

Me, too. Copy-and-paste from this message to the VBA editing pane.


----- original message -----

Rats, it's still not working. To answer your questions:

1) ClaimClients and ClaimStat are both Dim'd as ranges and assigned
using
"Set", as you assumed. The ClaimClients range contains 4-digit
numbers
and
the ClaimStat range contains O's and C's.
2) ClientNum is a VBA defined variable (Dim'd as Integer).

All those quotation marks cross my eyes. If you can help me get them
in
the
right spot I'll sure appreciate it.

:

I have a ranges named ClaimClients and ClaimStat and a
value named ClientNum.

[Sorry about the premature posting. Hit the wrong "button".]

Are those named Excel objects, or are they the names of VBA
variables?

If they are VBA variables, what are their definitions and how are
their
values assigned?

Presumably:

Dim ClaimClients as Range, ClaimStat as Range
Set ClaimClients = Range("a1:a10")
Set ClaimStat = Range("b1:b10")

If that is the case, then you should use ClaimClients.Address and
ClaimStat.Address in the Evaluate string. Failure to do so would
cause a
Type Mismatch error.

But you might have other errors, which, at the very least, might
cause
the
Evaluate function to fail to deliver the intended result.

It is unclear what type ClientNum is and how its value was assigned.

Your code snippet treats the value of ClientNum as a string. But is
that
truly your intent? Is that the type of values in the cells
represented
by
the range variable ClaimClients?

Also, at a minimum, you need "--" before each SUMPRODUCT parameter.

In summary, the following might be what you intended:

ActiveCell.Offset(0, 10).Value =
ActiveSheet.Evaluate("SUMPRODUCT(--(" &
_
ClaimClients & "=""" & ClientNum & """),--(" & ClaimStat &
"=""O""))")

FYI, that can also be written:

ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT(("
& _
ClaimClients & "=""" & ClientNum & """)*(" & ClaimStat &
"=""O""))")

However, if ClaimClients, ClaimStat and pehaps even ClientNum are
named
Excel objects, you should write:

ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT(("
& _
"ClaimClients=ClientNum)*(ClaimStat=""O""))")

Or if ClientNum is a VBA variable:

ActiveCell.Offset(0, 10).Value = ActiveSheet.Evaluate("SUMPRODUCT(("
& _
"ClaimClients=""" & ClientNum & """)*(ClaimStat=""O""))")

PS: You could write simply Evaluate instead of ActiveSheet.Evaluate.

If none of those comments help, please post more VBA context in your
response, and address the questions I raised above.


----- original message -----

I've looked through the various posts concerning this, but still
can't
quite
get this to work. I have a ranges named ClaimClients and ClaimStat
and
a
value named ClientNum. I want to find the number of times the
ClaimClients
range has a value of ClientNum and the ClaimStat range has a value
of
"O".
Here is the code at present:

ActiveCell.Offset(0, 10).Value =
ActiveSheet.Evaluate("SUMPRODUCT((" &
_
ClaimClients & "=""" & ClientNum & """),(" & ClaimStat &
"=""O""))")

With all the various things I've tried to make this work, I always
get
either a Type Mismatch error or a Syntax error.

.


.