SUMPRODUCT in VBA

[lexahoffner]

I have searched through past messages and tried several things, but I
always get #VALUE! as the end result. I am new to VBA, but this group
has already helped tremendously.

I am trying to sum up the instances where records match two criteria.
The criteria is spread between two sheets.


On one sheet I have a column that can range in value from 1 to 3. (the
rows go from 2 to 3990)
On that same sheet I have four columns next to eachother (same number
of rows) that have various values.

On a second sheet I have the possible values from the set of four
columns on the first sheet in numerical order in the first column
(starts at row 97 and ends at row 456)
On this same second sheet I have a header row that has 1 in the 2nd
column, 2 in the third and 3 in the fouth.

My desired end result is a table like this:

1 2 3
10 0 5 3
33 200 2 16
46 4 90 0
71 3 22 0

ie: there were 0 records on the first sheet that had 1 in that 1st
column and 10 in one of the 4 other columns...there were 5 records that
had a 1 in that 1st column and 10 in one of the 4 other columns.

Here is my code so far: And all I get is #VALUE! in my resulting
table.


Sub subnetsnets()
Dim Answers As Range
Dim Cohort As Range


Set Cohort = Sheet5.Range("b2:b3990")
Set Answers = Sheet5.Range("l23990")

For r = 97 To 456
For CohortC = 3 To 5
Cells(r, CohortC).Value =
Evaluate("sumproduct(Cohort=(cells(4,CohortC).value)*(Answers=(cells(r,1).value))")

Next CohortC
Next r


End Sub

 

[Tom Ogilvy]

Evaluate("sumproduct((" & Cohort.Address(0,0,xlA1,True) & "=" & _
cells(4,CohortC).value & ")*(" & Answers.Address(0,0,xlA1,True) & _
"=" & cells(r,1).value & "))")


I don't know if this will give you the correct answer, but it will give you
the sumproduct formula you appear to be trying to write.

for example if sheet5 of Book2 has a tab name of House it produces:

sumproduct(([Book2]House!B2:B3990=3)*([Book2]House!L2:O3990=33))

 

[lexahoffner]

Tom - Thank you so much!

I have put it in and it returns values!! I have been working on this
for 3 days and couldn't get a value.

Now I am trying to understand your code.

what are the ampersands for? and are the quotes a vba requirement
for other functions besides sumproduct?

Thanks again for your help! -- my forehead was turning black and blue
from banging it on the desk.

Lexa

 

[Tom Ogilvy]

& are for concatenation. You want to concatenate in the addresses of the
ranges to your formula so your formula would work if entered in a cell in a
worksheet.