+ Reply to Thread
Results 1 to 12 of 12

Find multiple "text" criteria and return as ""Yes" in Matrix

  1. #1
    Registered User
    Join Date
    04-27-2012
    Location
    Sweden
    MS-Off Ver
    Excel 2007
    Posts
    29

    Find multiple "text" criteria and return as ""Yes" in Matrix

    Hi,

    I have columns that tells me which courses specific people have passed.

    I would like this information to result in a matrix for better overview.

    Please have a look at the attached file.

    Br Bertrand
    Attached Files Attached Files

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Find multiple "text" criteria and return as ""Yes" in Matrix

    Try:

    =IF(COUNTIFS($A$4:$A$9,F$3,$B$4:$B$9,$E4,$C$4:$C$9,"Passed"),"Yes","")

    copied down and across.

    Note: You have Eric(Erik) spelled 2 different ways....
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    04-27-2012
    Location
    Sweden
    MS-Off Ver
    Excel 2007
    Posts
    29

    Re: Find multiple "text" criteria and return as ""Yes" in Matrix

    Unfortunately i get a formula error message.

    Br

    Please view attached jpeg.
    Attached Images Attached Images

  4. #4
    Registered User
    Join Date
    04-26-2012
    Location
    Norwalk, CT
    MS-Off Ver
    Excel 2003
    Posts
    45

    Re: Find multiple "text" criteria and return as ""Yes" in Matrix

    Use this formula in F4 =IF(SUMPRODUCT(($A$4:$A$9=F$3)*($B$4:$B$9=$E4)*($C$4:$C$9="Passed"))=1,"Yes","No")

    As previous poster noted, Eric will not work since it's spelled differently

  5. #5
    Registered User
    Join Date
    04-27-2012
    Location
    Sweden
    MS-Off Ver
    Excel 2007
    Posts
    29

    Re: Find multiple "text" criteria and return as ""Yes" in Matrix

    Hi,

    thank you but still have some problems with the formula.

    Br
    Attached Images Attached Images

  6. #6
    Registered User
    Join Date
    04-26-2012
    Location
    Norwalk, CT
    MS-Off Ver
    Excel 2003
    Posts
    45

    Re: Find multiple "text" criteria and return as ""Yes" in Matrix

    I'm not sure why...both formulas that NBVC and I presented should have worked.

    I've attached the file I did the formula in
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    04-27-2012
    Location
    Sweden
    MS-Off Ver
    Excel 2007
    Posts
    29

    Re: Find multiple "text" criteria and return as ""Yes" in Matrix

    Strange... your file works, thank for the assistance!

    Br

  8. #8
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Find multiple "text" criteria and return as ""Yes" in Matrix

    It is probably because your excel version uses semi colons as argument separators instead of commas.

  9. #9
    Registered User
    Join Date
    04-27-2012
    Location
    Sweden
    MS-Off Ver
    Excel 2007
    Posts
    29

    Re: Find multiple "text" criteria and return as ""Yes" in Matrix

    Regarding:

    =IF(SUMPRODUCT(($A$4:$A$9=F$3)*($B$4:$B$9=$E4)*($C$4:$C$9="Passed"))=1,"Yes","No")

    If I want to find $A$4:$A$9 in another sheet with name "matrix data", How do I add this to the formula?

    Br

  10. #10
    Forum Contributor
    Join Date
    11-26-2011
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    159

    Re: Find multiple "text" criteria and return as ""Yes" in Matrix

    Hi,

    find the attachemnt

    and U can add multiple conditions.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    04-27-2012
    Location
    Sweden
    MS-Off Ver
    Excel 2007
    Posts
    29

    Re: Find multiple "text" criteria and return as ""Yes" in Matrix

    Okey, I have made it a little more difficult.

    Please view attached file
    Attached Files Attached Files

  12. #12
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Find multiple "text" criteria and return as ""Yes" in Matrix

    Try:

    =IF(SUMPRODUCT((Data!$A$4:$A$20=C$4)*(Data!$C$4:$H$20=$B6)*(Data!$D$4:$I$20="Passed")),"Yes","No")

    adjust ranges to suit and copy down and to next column(s).... note the last condition range is offset to the right by 1 column....

    replace commas with semi-colons if needed in your version.

    Note: Also Sumproduct is not the most efficient method for ranges so large. If you can add a column that identifies which header in the Data sheet the information is in, then you can use COUNTIFS which is much more efficient. See attached columns A, F and G in the Matis sheet.

    In F4, formula is:

    Please Login or Register  to view this content.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1