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

u4carson

New Member
Joined
May 21, 2018
Messages
12
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>
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

markmzz

MrExcel MVP
Joined
May 7, 2011
Messages
3,699
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

New Member
Joined
May 21, 2018
Messages
12
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

MrExcel MVP
Joined
May 7, 2011
Messages
3,699
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

New Member
Joined
May 21, 2018
Messages
12
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!!!
 

Watch MrExcel Video

Forum statistics

Threads
1,102,326
Messages
5,486,172
Members
407,536
Latest member
farrukhikram

This Week's Hot Topics

Top