Thread: Count unique values (in an ever expanding list) based on criteria

Hi all,

I have attached a dummy workbook for reference....

I have a series of record entries for ‘bottle tests’. I need a formula to provide the total number of unique entries (entries meaning the ‘bottle number’ from column A) based on specific criteria (the ‘size’ listed in column B, there are 4 different sizes).

I have tried a variety of formulas, however; they return errors or inaccurate information because it does not allow for blank cells. Also, these formulas mean that I have to filter for each one of the 4 criteria then paste to a new page in order for them to work (inaccurate as they may be…)

I need to search the range [A:A], not [A2:A1387] because the list will be updated frequently and I want the formula to self-adjust as the end-users will not be able to do any filtering, copying pasting etc..

The end result should be 4 totals:

Total number of unique bottle numbers (column A) for each criteria (column B)

Example:
336 unique entries for 38mm
210 unique entries for 45mm
Etc.

Thanks in advance for any help….

If you list the different sizes in F2 down then you can use this formula in G2

=SUM(IF(FREQUENCY(IF(B$2:B$2000=F2,IF(A$2:A$2000<>"",MATCH(A$2:A$2000,A$2:A$2000,0))),ROW(A$2:A$2000 )-ROW(A$2)+1),1))

confirmed with CTRL+SHIFT+ENTER and copied down

Note: you can use whole column references with that formula in Excel 2007 and later only but I don't recommend it - the formula will be a lot slower - best to pick a range that is bigger than you need like 2000 rows.....or define some dynamic ranges that can be used in the formula - they will expand automatically as required