# returning a value if it meets conditions

1. ## returning a value if it meets conditions

Hi there! I'm building a certification training tracker that essentially tracks the name of people, the certificates they've completed, and the expiry date of the certification.

I'm trying to figure out a formula that will return the name of a person if they have completed AT LEAST 4 training certifications that HAVE NOT expired (for reference ,red means that they will expire in less than 1 month, green means they will expire in more than one month, and orange highlights mean that they have already expired)

the formula I could come up with so far is : =IFERROR(INDEX(\$A\$4:\$A\$12,SMALL(IF(B4:L12>TODAY(),ROW(\$B\$4:\$B\$12)-ROW(\$B\$4:\$L\$12)+1),ROWS(\$B\$4:\$L\$12))),"") --> but this did not give me the right names

THANK YOU SO MUCH FOR YOUR HELP!!!!

2. ## Re: returning a value if it meets conditions

Welcome to the forum!

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

3. ## Re: returning a value if it meets conditions

Thanks for letting me know-- I've gone ahead at attached the document

4. ## Re: returning a value if it meets conditions

Create a formula that SUMs the cells where Target Date is Greater Than EACH Certificate Date. Perhaps in Column M or N put
- maybe put target date in cell L1 . You can modify the formula and add the the cells i didn't include (G through L). But by way of example this should get you going. I should have added formula to compare Sum is greater than 4..... perhaps = if( x >4, "Has 4 or More", "has less than 4")

5. ## Re: returning a value if it meets conditions

Thanks for the suggestion! However, I was hoping to have the names of the people (ie those under column A) come up in the B15 (Names of those Certified). I have the formula for the number of fully certified members with 4 or more certifications, but now i'm hoping to devise a formula where by it will give me the names of those four people.

6. ## Re: returning a value if it meets conditions

If you make your list a Filtered Table, then you will have the names. For example, if you have the formula in Column M, then you could filter that column for those with the sum of 4 or More... your names will hide those not having less than 4, and show those having more than 4. Work with me....! : )

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