excelexcel-formula

Excel filter data in date intervals excluding all 0 values

I would like to filter out data based on time intervals (days), excluding the rows that have all 0 values in that time span, so for example from the following file:

1/5/2025 1/6/2025 1/7/2025 1/8/2025 1/9/2025 1/10/2025 1/11/2025 1/12/2025 1/13/2025 1/14/2025 1/15/2025 1/16/2025 1/17/2025 1/18/2025 1/19/2025
A 11 11 11 11 10 10 10 9 8 8 8 7 7 7 6
B 16 15 15 15 14 14 14 13 12 12 12 11 11 11 10
C 0 0 0 3 2 2 2 1 0 0 0 0 0 0 0
D 0 0 4 0 0 0 0 0 0 0 0 0 0 0 0
E 0 0 0 7 0 0 0 0 0 0 0 0 0 0 0
F 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
G 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

If I wanted to extract data from 1/8/2025 to 1/12/2025 I would only obtain A,B,C,E rows data. If I wanted to extract data from 1/9/2025 to 1/10/2025 I would obtain only A,B,C rows data.

I would like to find a FORMULA/FUNCTION so that it can be done automatically. I tried with FILTER, but unfortunately I don't know how to specify the condition on null values.

Thks

Solution

  • Here is one way to achieve the desired output using BYROW() Function:

    enter image description here

    • Formula used in cell U1

    =LET(
     a, B1:P1,
     b, FILTER(B1:P8,(a>=R1)*(a<=S1)),
     FILTER(HSTACK(A1:A8,b),BYROW(b,LAMBDA(x,OR(x>0)))))