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

1. ## 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

2. ## 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....

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

Unfortunately i get a formula error message.

Br

4. ## 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. ## Re: Find multiple "text" criteria and return as ""Yes" in Matrix

Hi,

thank you but still have some problems with the formula.

Br

6. ## 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

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

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

Br

8. ## 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. ## 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. ## Re: Find multiple "text" criteria and return as ""Yes" in Matrix

Hi,

find the attachemnt

and U can add multiple conditions.

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

Okey, I have made it a little more difficult.

12. ## 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.``

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

#### 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