# Thread: Excel Count Unique cells with given criteria

1. ## Excel Count Unique cells with given criteria

 A B C D E F 1 258 AA Apple 147 AA Orange 189 BB Apple 2 1 2 3 4 5 6 3 236 AA Grapes 151 BB Pear 189 BB Apple 4 2 8 4 7 6 1 5 258 AA Apple 486 BB Grape 147 AA Orange 6 5 6 8 2 7 9 7 8
In respect to the above table, I need to write a formula in A8, which can evaluate and the count the cells A1, C1, E1, A3, C3, E3, A5, C5, E5 , where the formula should only count those cells ending with "AA" and also with no duplicates.
Means the formula result must be 3
Totals cells evaluated = 9, Cells that ends with "AA" = 5, In that 258 AA (A1 & A5) and 147 AA (C1 & E3) is repeated twice, which has to be counted only once. so the result is 3.
Can any one help me in writing such a formula. I tried different methods but none worked.  Reply With Quote

2. Help us to help you - upload a sample workbook.   Reply With Quote

3. Test this one thoroughly; I haven't:
Code:
`=SUMPRODUCT(--(RIGHT(A1:F6,2)="AA"),MMULT(--(ISODD(ROW(A1:F6))),--(ISODD(COLUMN(A1:F6)))),1/COUNTIF(A1:F6,A1:F6))`
and simpler:
Code:
`=SUMPRODUCT((RIGHT(A1:F6,2)="AA")*ISODD(ROW(A1:F6))*ISODD(COLUMN(A1:F6))*1/COUNTIF(A1:F6,A1:F6))`  Reply With Quote

count, criteria, excel, unique 