Or you could read the ranges into VBA arrays, and do the necessary
calculations on them. I wouldn't bother with sumproduct I don't think. I'd
just loop, do my comparisons within the loop, and keep score with a counting
variable.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"JE McGimpsey" wrote in message
...
One way:

If you're using XL07, you could use COUNTIFS().

or, you could enter the array formula in the worksheet, then read the
value back, e.g., for

=SUMPRODUCT(--(A1:A40="A"),--(B1:B40=1))

use

Const sFORMULA As String = _
"=SUMPRODUCT(--(^1=^2),--(^3=^4))"
Dim rRange1 As Range
Dim rRange2 As Range
Dim nResult As Long
Dim sCrit1 As String
Dim sCrit2 As String

Set rRange1 = Range("A1:A40")
sCrit1 = """A"""
Set rRange2 = Range("B1:B40")
sCrit2 = "1"
With Range("Z1")
.FormulaArray = Replace(Replace(Replace(Replace( _
sFORMULA, "^4", sCrit2, 1), "^3", rRange2.Address, 1), _
"^2", sCrit1, 1), "^1", rRange1.Address, 1)
nResult = .Value
.Clear
End With
MsgBox nResult

Or you could do it all in a variant array. Using the example above:

Dim vArr As Variant
Dim i As Long
Dim sCrit1 As String
Dim nCrit2 As Long
Dim nResult As Long

sCrit1 = "A"
nCrit2 = 1
vArr = Range("A1:B40").Value
For i = 1 To UBound(vArr, 1)
If vArr(i, 1) = sCrit1 Then _
nResult = nResult - (vArr(i, 2) = nCrit2)
Next i
MsgBox nResult



In article ,
John wrote:

Ok, I used the CountIf function and it worked. However, I will need to
use
the SumProduct function to compare multiple ranges in the future. Is
there
anyway around this problem?