Excel Count Unique cells with given criteria

N

nixz

New member
Joined
Feb 21, 2020
Messages
1
Reaction score
0
Points
0
Excel Version(s)
2010
ABCDEF
1258 AAApple147 AAOrange189 BBApple
2
123456
3236 AAGrapes151 BBPear189 BBApple
4284761
5258 AAApple486 BBGrape147 AAOrange
6568279
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.
Thanks in advance.
 
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))
 
Last edited:
You must log in or register to reply here.
Back
Top