SUMPRODUCT

[Mark]

Can someone asist me with this formula, please?

I have used the formula:

=IF(COUNTIF(RecordedDaily!$I$2:RecordedDaily!I2,RecordedDaily!I2)=1,RecordedDaily!I2,"")

supplied by Chip Pearson to find unique values in a worksheet and populate
another worksheet.

I now want to use sumproduct formula to find the number of instances with N
and whatever the value that Chip formula gave in the cell range ("B21").

Sheets("Violence").Range("C21").FormulaR1C1 = _

"=SUMPRODUCT(--(RecordedDaily!R2C8:R1000C8=""N""),--(Violence!!R21C2:R50C2=Violence!Cells(2:21).value))"

Going wrong somewhere!

 

[Bob Phillips]

Maybe

Sheets("Violence").Range("C21").FormulaR1C1 = _
"=SUMPRODUCT(--(RecordedDaily!R2C8:R1000C8=""N"")," & _
"--(Violence!R21C2:R50C2=""" &
worksheets("Violence").Range("U2").Value & """))"


--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)