30000 lines will take some time using SUMPRODUCT. Not sure if that is the best method at this point, but let's continue down this path, for now.

First, I created a test sheet, and the formula you have should work. I did remove the reference to the Summary sheet, but that should not cause a problem (unless you don't have a sheet named Summary). Does your source data have #N/As in it? Look carefully, as I think this may be the issue since the formula seems sound.

Second, it looks like you are trying to use a wildcard for Imprivata. The check with an '=' will look for an exact match (including the *). If you want it to be anything that starts with Imprivata, then you need to use something similar to the LEFT statements that were used to find "PC" and "WC".

Finally, it's time to use 'Evaluate Formula' to try to find the source of the #N/A. To do that, first modify your equation to reduce the size of the ranges. Essentially, replace every '30000' with '3'. Then go to the Formulas ribbon and hit the 'Evaluate Formula' button. It will allow you to step through the formula and determine where the N/A is originating (if not from the source data).