1. Your type mismatch is from multiple instances in the sumproduct line where you're trying to get arrays (zz=ws.Range("AX2:AX" & lRow) = "value" alone will get you a mismatch error).
2. You're looking for speed; your vba may not get you much extra speed because you're repeatedly reading from the sheet (and evaluating the same things over and over). This can be streamlined a lot.
3. It's difficult to guess what you want the code to do, from code which isn't doing what you want! I suggest you attach a workbook, with say, some 10 rows of data, which includes your existing (and working) formulae. Highlight the range which needs to be replaced by vba. The workbook should include only the code related to this problem (so that we don't have to go hunting for it) and only the sheets needed for this problem.
4. It may also help us if you say in words what the results are meant to show.
5. What version of Excel are you using?

The bits coloured in this colour are what I'd like to see in your reply.