Matching data and returning count
Matching data and returning count
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Matching data and returning count

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I have a set of criteria values in Column A and would like to insert a formula into column B which matches them to a LOOKUP list and counts how many entries DO NOT meet a criteria

    E.g. Counting the number of times cat, sat or mat (cell A1:A3) appears in the LOOKUP list when the value is NOT EQUAL to 0


    TABLE
    A B
    1 cat *
    2 sat *
    3 mat *

    LOOKUP LIST
    Critria Value
    cat 0
    mat 1.5
    mat 0
    sat 2
    cat 2
    cat 3
    sat 2


    would return

    A B

    1 cat 2
    2 sat 2
    3 mat 1



    Anyone help??


  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,778
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

      
    JJ,

    Lets A2:A4 house

    {"cat";
    "sat";
    "mat"}

    and F1:G8

    {"Criteria","Value";
    "cat",0;
    "mat",1.5;
    "mat",0;
    "sat",2;
    "cat",2;
    "cat",3;
    "sat",2}

    In B2 enter & copy down:

    =SUMPRODUCT(($F$2:$F$8=A2)*($G$2:$G$8>0))

    This will give you a count of the co-occurrences of an item with associated non-zero values.

    Aladin

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com