Count number of unique values in Column A that match a term in Column B

Our company ships items, and we have two statuses: "prep" for items that still need to be washed and cleaned, and "ship" for items that need to be boxed. We want to know how many products still need to be prepped, and the ones that need to be shipped, so we have one column that either shows "prep" or "ship", and another column with the name of the product. Each row represents one physical item, so if we have 10 items, there will be 10 rows.

Getting the number of physical items that need prepping or shipping was easy, we just used this formula:

Please Login or Register  to view this content.

However, I want to find the number of unique products in the product column, but *only* if the value in the prep/ship column contains either "prep" or "ship".

Let me show you the steps I have done so far:

It looks like this is the closest formula that I can find, using the FREQUENCY formula: https://exceljet.net/formula/count-u...ues-in-a-range

Using this information, I came up with this formula:

Please Login or Register  to view this content.

But of course that is not working.

G is the prep/ship column, and J is the product column.

The page I referenced above said that the FREQUENCY formula is more complex, but computes much faster, so I'm looking for a formula that will do this computation without bogging down our already slow workbook. Any ideas?

Awesome! That did the trick! Thanks!