Results 1 to 3 of 3

Thread: Excel Count Unique cells with given criteria

  1. #1
    Neophyte nixz's Avatar
    Join Date
    Feb 2020
    Posts
    1
    Articles
    0
    Excel Version
    2010

    Excel Count Unique cells with given criteria



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.
    Thanks in advance.

  2. #2
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,376
    Articles
    0
    Excel Version
    Office 365 Subscription
    Help us to help you - upload a sample workbook.
    Ali
    Enthusiastic self-taught user of MS Excel!

  3. #3
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,691
    Articles
    0
    Excel Version
    365
    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 by p45cal; 2020-02-21 at 06:30 PM.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •