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("l2:o3990")

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

Next CohortC
Next r


End Sub