vlookup and sumifs or count ifs

    Hi


    I have a work book with two worksheets.


    the master data tab and a summary table tab


    I need a formula that can do a vlookup so using an id number in my summary table, locate that id number in the master table it will appear multiple times and then I need it to look at another column in my master data and count if for that id number the other column has a specific text string so for example.


    for id number A09669 there are a count of 3 entries for the action a written comment and for the same ID a count of 1 entry for the Action Kept Behind


    please see attached

    Re: vlookup and sumifs or count ifs


    Assuming your version of Excel is recent enough to have the CountIfs function you can use the following example for your summary formula in cell M2 and fill down.
    =COUNTIFS('master data'!$A$2:$A$102,$A2,'master data'!$E$2:$E$102,"Written Comment")

    Re: vlookup and sumifs or count ifs


    Another possibility could be
    M2=SUMPRODUCT(('master data'!$A$2:$A$102=$A2)*('master data'!$E$2:$E$102=M$1))
    Take care that headers in "summary table" are corresponding to the right text in "master data"

    Triumph without peril brings no glory: Just try

    Re: vlookup and sumifs or count ifs


    Brilliant got to the Countifs to work eventually I couldn't use the SUMProduct as the headers differed. thanks again guys you are brilliant :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login