Selecting date come data

[Suresh Badhani]

If i select date retrieve WOFF against every TL.

 

[Suresh Badhani]

plz suggest

 

[vletm]

Suresh Badhani
One sample in yellow...

 

[bosco_yip]

In K3, formula copied down :

=COUNTIF(OFFSET(A$1,MATCH(J3,A:A,0)-1,MATCH(K$1,B$1:H$1,0),COUNTIF(A:A,J3)),"WOFF")

 

[Suresh Badhani]

In K3, formula copied down :

=COUNTIF(OFFSET(A$1,MATCH(J3,A:A,0)-1,MATCH(K$1,B$1:H$1,0),COUNTIF(A:A,J3)),"WOFF")

View attachment 78769
[/QUOT
Thanks a lot a lot of thnaks to help

 

[Suresh Badhani]

Suresh Badhani
One sample in yellow...

so great help thanks lot dear.

 

[Peter Bartholomew]

For those who can use 365, XLOOKUP will return a column as a range reference. That allows the developer to use CONTIFS to count the instances of "WOFF", just as did @bosco_yip and @vletm.

= COUNTIFS(TL, distinctTL, XLOOKUP(selectedDate,dates,data), "WOFF")

Then again, one could get more ambitious and combine the count with the process of drawing up a list of distinct TLs as a single dynamic range

= WOFFλ(selectedDate)

where

WOFFλ = LAMBDA(d,
   LET(
      distinctTL, SORT(UNIQUE(TL)),
      dataDay, XLOOKUP(d,dates,data),
      WOFF, COUNTIFS(TL, distinctTL, dataDay, "WOFF"),
      HSTACK(distinctTL, WOFF)
   )
);

 

[Suresh Badhani]

So many thanks friend