Issue with nesting Sum If

[repke]

I tried using this array formula but it has an error in it and I can't figure out the error. =SUM(IF(Tracking!$C$2:$C$5000=$B$4,IF((Tracking!$D$2:$D$5000="15-M Eval")+(Tracking!$D$2:$D$5000="15-M TIC")+(Tracking!$D$2:$D$5000="Misd CRT"),IF(Tracking!$E$2:$E$5000=$A6,IF(Tracking!$J$2:$J$5000>=VALUE($B$1),IF(Tracking!$J$2:$J$5000<=VALUE($B$2),1,0)))))) Any suggestions on what I'm doing wrong? (I attached the workbook. In the CSTE worksheet, I didn't use control-shift-enter so you could see the formula.)

What I'm trying to do is come up with a formula that will sum if either of these three Legal Status types (15-M Eval, 15-M TIC, and Misd CRT) are present on the Tracking worksheet with a specified Evaluator within a certain date range. The formula works if I just want to find one Legal Status type. I also will want to come up with a formula that will sum if either of these five Legal Status types (15/30-F Eval, 15-F TIC, 1/90 CRT, 2/90 CRT, and 180 CRT) are present on the Tracking worksheet with a specified Evaluator within a certain date range. I tried researching if I could define a name that would sum these 3 Legal Status types but that didn't work either.

Any help is much appreciated. Di

[NBVC]

It seems to work for me after I confirm it with CTRL+SHIFT+ENTER.

I get a 1 which is for the item in Row 5 of the Tracking Sheet... the only match..

If I change Row 8 name to Adam, then I get a 2 result for the formula since now Row 5 and Row 8 of Tracking sheet match your criteria.

Is that right or wrong?

[repke]

You're right! I've tried this so many times and it hasn't worked I have no idea why it is working now but I'm glad.