Count Within Set of Cells

[staffsmatt]

Hi,

We have a sheet that represents outstanding orders. Each order could be one or more lines long. I've created a helper column in Column D to make it easier to identify individual orders.

I am trying to create a formula that identifies order lines that:

Are not ready to pick (No in column B in the attached)
Are already in WIP (YES in column C in the attached)
Are the last line needed to complete that order - it is this third part that I can't work out.

What we want is to only put a yes in column F if that is the last line waiting for that order. So in the attached F28 would be YES (that line not ready to pick, is already in WIP and is the last line for that order) F37 and F41 should not have a YES though as they are both for the same order and so there is more than one line waiting.

I've tried a few things like countif but I'm not sure how to get it to restart the count for each order...

Thanks!

Count Within Order.xlsx

[Pete_UK]

Try this in F2:

=IF(COUNTIF(A$2:A2,LEFT(A2,FIND("-",A2)-1)&"*")=COUNTIF(A:A,LEFT(A2,FIND("-",A2)-1)&"*"),"yes","")

Copy down as required.

Hope this helps.

Pete

[oeldere]

F2=COUNTIF($A$2:$A2,$A2)=COUNTIF($A$2:$A$4697,$A2)

G2=IF(AND($B2=$K$1,$C2=$L$1,$F2=$M$1),"yes","")

See the attached file.

[staffsmatt]

Hi Both,

Thanks for the formulas. Unfortunately neither work quite right.

Pete_UK this seems to just highlight where the conditions are met for the individual line and not take into account if it is the last line for the order.

oelder this seems to almost give the result I'm after but I think it is just looking to see if the line is unique rather than if all of the other order lines are Ready to Pick (for example row 3025 should not be shown as "yes" as there are a number of other lines on that order which are not ready to pick either.

Thanks again

Matt

[oeldere]

oeldere this seems to almost give the result I'm after but I think it is just looking to see if the line is unique rather than if all of the other order lines are Ready to Pick (for example row 3025 should not be shown as "yes" as there are a number of other lines on that order which are not ready to pick either.

Info of line 3025
226868-22 NO YES 361 TRUE TRUE yes


1) What did you expect as result?

2) and why did you expect that result?

[staffsmatt]

HI,

Expected result would be:

226868-22 NO YES 361 TRUE TRUE blank

Reason is that the following lines (which are the same order) are not available to pick (column B is no)

3016
3017
3018
3019
3020
3021
3022
3024
3026
3027
3028

Thanks

[oeldere]

Cell F2=COUNTIF($D$2:$D2,$D2)=COUNTIF($D$2:$D$4697,$D2)

[staffsmatt]

Hi oeldere, that seems to be working great.

Thanks very much for your help.

[oeldere]

Glad I could help.

If you are satisfied with the solution, please mark the question solved.

You can add reputation by clicking on the star * add reputation , to all members who helped you.