• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

Peter Bartholomew

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

To get this required a drastic change of formula in order to identify the range over which to count occurrences of "wip".
Code:
= 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.
 

Attachments

Naveen BN

New Member
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.
 
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")
 

Attachments

XOR LX

Active Member
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

Well-Known Member
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.
Code:
Defined Name 'isAction':
= COUNTIFS(eventList, status)

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

Attachments

Last edited:
Top