Help required on getting the Count Continuous Non-Blank cells with multiple criteria which appear Last from a row

[Naveen BN]

Hi team, attached the revised file, sorry for the inconvenience. this is the base file which is maintained. please help on the formula

 

[Peter Bartholomew]

The rules of this game get more involved each time I look! Working from the data in @Excel Wizard #20 I have got to


To get this required a drastic change of formula in order to identify the range over which to count occurrences of "wip".

= LET(
  event, IF((status<>"")* (status<>"wip"),"action", status),
  finalAction, XLOOKUP("action", event, status, , ,-1 ),
  finalWIP, XLOOKUP("wip", event, status, , ,-1 ),
  locatedWIP, COLUMN(finalWIP) >  COLUMN(finalAction),
  days, COUNTIFS( finalAction:finalWIP, "wip" ),
  IF(locatedWIP, days, 0) )

The first line eliminates blanks and "wip" from being considered as 'actions'
The next returns the cell reference for the final action
Then the cell reference for the final "WIP"
The cell references are used to define the range over which "wip" is counted
A check is made of which event is the first (should be action then wip)
Return the number of days.

I think the discrepancy arises because I have treated "awaiting confirmation" as an event that limits the range.

I have attached a file for anyone with a recently updated version of Excel 365.

 

[Naveen BN]

Hi Peter, Have worked with the formula on from @Excel Wizard #20 , still the output what is expected am not getting that, can please help. have attached the latest working on the file #26, the number of rows increases it stuck with 3 days unable to understand. please help me to understand & please check the latest file uploaded #26 with working of XOR Formula & Excel Wizard's formula.

 

[Excel Wizard]

This is the same formula in Post #20 just change AF to BJ

BN2
=TEXT(IFNA((LOOKUP("Ω",B2:BJ2)="wip")/LOOKUP(2,1/FREQUENCY(IF(B2:BJ2="wip",COLUMN(B2:BJ2)),IF((B2:BJ2<>"")*(B2:BJ2<>"Wip"),COLUMN(B2:BJ2)))),),"[>1]0 \Da\y\s;0 \Da\y")

 

[XOR LX]

Also:

=TEXT(COUNTIF(BJ2:INDEX(A2:BJ2,1+MATCH(1,0/(IF(A2:BJ2="","wip",A2:BJ2)<>"wip"))),"wip"),"[=1]0 \Da\y;0 \Da\y\s")

Regards

 

[Peter Bartholomew]

Unless you have LET and XLOOKUP this will error as soon as you attempt to recalculate the formula.

 

[Peter Bartholomew]

Just in case a solution is still needed, I have both simplified the LET formula and, more importantly, reformulated a version of the solution to avoid LET and use LOOKUP in place of XLOOKUP.

Defined Name 'isAction':
= COUNTIFS(eventList, status)

Worksheet Formula:
= COUNTIFS(
  status, "wip",
  period, ">" & IFNA( LOOKUP(2,1/isAction,period), startDate-1) )

 

[Naveen BN]

Thank you all for you kind support. the formula worked which was shared by you. thank you