# Thread: Formula to identify status of requisition when there are multiple possible scenerios Thanks: 0 Likes:  1 Post #5072799 (1)

1. ## Formula to identify status of requisition when there are multiple possible scenerios

I am hoping to find assistance with a formula that will help me identify which requisitions have been fully or partially awarded.

Each requisition may have multiple lines and none, some, or all of the lines might be awarded. In the example below Requisition 111111 has three line items, but only line three has been awarded so the requisition is "partially awarded." Requisition 444444 has three line items and no lines have been awarded so it is "not awarded."

The difficulty I am having is that I need each instance of the requisition to reflect this information. For instance with requisition 111111, line items 1, 2 and 3 must all state the same status (in this case partially awarded) even though only one line has an award number associated with it.

As a note, there can be more than one award number per requisition as shown in example 66666 below.

I am looking for a formula to complete the "Requisition Status" column in the image below.

Thank you.
 REQUISTION LINE AWARD NUMBER REQUISITION STATUS 111111 1 - PARTIAL AWARD 111111 2 - PARTIAL AWARD 111111 3 123111 PARTIAL AWARD 444444 1 - NOT AWARDED 444444 2 - NOT AWARDED 444444 3 - NOT AWARDED 666666 1 223111 AWARDED 666666 2 223111 AWARDED 666666 3 333222 AWARDED

2. ## Re: Formula to identify status of requisition when there are multiple possible scenerios

Try this:

=IF(COUNTIF(\$A\$2:\$A\$10,A2)=COUNTIFS(\$A\$2:\$A\$10,A2,\$C\$2:\$C\$10,">0"),"",
IF(COUNTIFS(\$A\$2:\$A\$10,A2,\$C\$2:\$C\$10,">0"),"PARTIAL ","NOT "))&"AWARDED"

Markmzz

3. ## Re: Formula to identify status of requisition when there are multiple possible scenerios

Markmzz,

I failed to mention that the award numbers contain text which cause your formula (which otherwise does exactly what I need) to not work. A sample award number is 123C7818F0087. If I substitute the award number with a number your formula works, otherwise it doesn't recognize the field. I apologize, I should have mentioned this in my original post. Any ideas? Thanks!

4. ## Re: Formula to identify status of requisition when there are multiple possible scenerios

Originally Posted by u4carson
Markmzz,
I failed to mention that the award numbers contain text which cause your formula (which otherwise does exactly what I need) to not work. A sample award number is 123C7818F0087. If I substitute the award number with a number your formula works, otherwise it doesn't recognize the field. I apologize, I should have mentioned this in my original post. Any ideas? Thanks!
Try this is this case:

=IF(COUNTIF(\$A\$2:\$A\$10,A2)=COUNTIFS(\$A\$2:\$A\$10,A2,\$C\$2:\$C\$10,"<>-"),"",
IF(COUNTIFS(\$A\$2:\$A\$10,A2,\$C\$2:\$C\$10,"<>-"),"PARTIAL ","NOT "))&"AWARDED"

Markmzz

5. ## Re: Formula to identify status of requisition when there are multiple possible scenerios

Originally Posted by markmzz
Try this is this case:

=IF(COUNTIF(\$A\$2:\$A\$10,A2)=COUNTIFS(\$A\$2:\$A\$10,A2,\$C\$2:\$C\$10,"<>-"),"",
IF(COUNTIFS(\$A\$2:\$A\$10,A2,\$C\$2:\$C\$10,"<>-"),"PARTIAL ","NOT "))&"AWARDED"

Markmzz
MArkmzz,

This worked! Thank you! I learned something today!!!

6. ## Re: Formula to identify status of requisition when there are multiple possible scenerios

Originally Posted by u4carson
MArkmzz,

This worked! Thank you! I learned something today!!!
You are welcome and thank you for the feedback.

Markmzz