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

[u4carson]

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

<tbody>
</tbody>

 

[markmzz]

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

 

[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!

 

[markmzz]

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

 

[u4carson]

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

 

[markmzz]

MArkmzz,

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

You are welcome and thank you for the feedback.

Markmzz