Formula to show 'Reject' number and type

I’m looking for formulas to populate “Times Rejected/Canceled” and “Rejected at Queue” Columns B & C in Report worksheet based on the data available in Queue worksheet.

Refer to the attached file, In the Report sheet, Column B gives you the number of times “Reject” or “Cancel” shows up against the ID and Column C gives you the Queue’s at which they were Rejected or Canceled.

Appreciate any help. Thanks!

You can put this formula in cell B2 of the Report sheet:

=SUM(COUNTIFS(Queue!C:C,$A2,Queue!E:E,{"cancel","reject"}))

and copy this down to give you a count of the number of "cancel" or "reject" records by production line.

For the second part, it would be easier to use a helper column in the Queue sheet, so I used this formula in cell G2:

=IF(OR(E2="cancel",E2="reject"),C2&"_"&COUNTIF(G$1:G1,C2&"_*")+1,"")

and copied this down to beyond the end of your data. Then you can use this formula in cell C2 of the Report sheet:

=IF(E2=0,"NONE",INDEX(Queue!$D:$D,MATCH($A2&"_1",Queue!G:G,0))&IFERROR(", "&INDEX(Queue!$D:$D,MATCH($A2&"_2",Queue!G:G,0)),"")&IFERROR(", "&INDEX(Queue!$D:$D,MATCH($A2&"_3",Queue!G:G,0)),"")&IFERROR(", "&INDEX(Queue!$D:$D,MATCH($A2&"_4",Queue!G:G,0)),""))

and copy this down. This will give you up to 4 Queue codes, but it is easy enough to extend this by copying the red part of the formula just inside the final bracket, and then changing the "_3" to a number in sequence as many times as you need to.

Hope this helps.

Actually, I've just noticed that your profile says that you use XL2003. I doubt this is still true, as you would not be able to post an .xlsx file, so please update your profile on UserCP, as IFERROR and COUNTIFs will not work on that version.

Pete

Worked perfectly, I liked that you gave additional tip to increase more Queue codes. Thank you.

I have updated my profile per your request.

Glad to hear that it worked for you.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete