I have a filtered list in which there are multiple options in Column A, Column B, amounts in Column C and other filtered labels in Column D. I have figured out how to calculate subtotals ignoring the hidden rows using the AGGREGATE function but I'm also trying to output the unique COUNTs of items with duplicate listings in Column A/B. I already have a formula below to count the unique instances in column B below (4 unique names).
B12=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(OFFSET(B1,1,0):B11,ROW(OFFSET(B1,1,0):B11)-ROW(OFFSET(B1,1,0)),,1)), IF(OFFSET(B1,1,0):B11>"",MATCH("~"&OFFSET(B1,1,0):B11,OFFSET(B1,1,0):B11&"",0))),ROW(OFFSET(B1,1,0):B11)-ROW(OFFSET(B1,1,0))+1),1))
What I want is a formula in B13, B14, B15 to count the unique instances with the selected Column A's unique value i.e. say you filtered Column A to be only "Normal", then the unique count of B12 would be 2 because Audi and Mercedes are the only two unique values...but Audi also shows up in filtered column A "special" so B13 desired output should actually be =1. B14 should =2 (BMW and Honda are the only unique values with "Special" in Column B) and B15 should =1 since Audi shows up in both Normal and Special Column A.
I've attached a sample sheet, i hope this makes sense / isn't too confusing for finding unique instances of text with filtered columns (obviously my actual data set is much wider and i plan to manipulate the filter table with other columns D-Z etc. unrelated to the column A/B that i want to find unique values of).
Bookmarks