Advice req: FILTER Function with Multiple OR Criteria

Question

Hello and greetings from my side.

I am using the FILTER functions with multiple criteria. I have one specific requirement where

1. I have to filter column C_1.......C_20 without ZERO count.
2. It means I have to use filter command with multiple creteria with OR .
3. I use the below syntex.. But it is working for 2 coulmns only.
4. =FILTER(DATA,(DATAC1>0)+(K48:K147>0),"N") --> it is working for two columns only not for other columns till c_20
5. Please advice how can i use the syntex. for all C_1 top C_20 columns.
6. Or else other options

Santosh

Answer 1

Hi @santosh chaturvedi

Regarding your issue, it seems like you want to keep only the rows where at least one column from C_1 to C_20 has a value greater than 0 and remove the rows where all columns are zero. 

Using + for OR logic works for a couple of columns, but it's not practical when you need to cover 20. There are two cleaner approaches you can try. 

1. Using BYROW (Excel 365) 

=FILTER(A2:W100, BYROW(D2:W100, LAMBDA(r, MAX(r)>0)), "N") 

2. Using MMULT (Excel 365 / Excel 2021) 

=FILTER(A2:W100, MMULT(D2:W100, SEQUENCE(20,1,1,0))>0, "N") 

How to apply: 

• Replace A2:W100 with your full data range 
• Replace D2:W100 with the range that covers your C_1 to C_20 columns only 
• If using Option 2, make sure the 20 in SEQUENCE(20,1,1,0) matches the number of C columns you have 

Note: If Option 1 returns a #NAME? error, it means your Excel version does not support BYROW. In that case, please use Option 2 instead. 

I've tested both formulas on my end using a small sample with 6 rows and 5 columns (C_1 to C_5). The sample data has 2 rows with all zeros and 4 rows with values greater than 0. 

Using BYROW: 

=FILTER(A2:H7, BYROW(D2:H7, LAMBDA(r, MAX(r)>0)), "N") 

The result correctly returned only the 4 rows that have at least one value greater than 0, and the 2 all-zero rows were filtered out. 

Using MMULT: 

=FILTER(A2:H7, MMULT(D2:H7, SEQUENCE(5,1,1,0))>0, "N") 

Same result, the 4 rows with values were kept and the 2 all-zero rows were removed. 

Both formulas produce the same output, so you can use whichever one works in your version of Excel.  

Hopefully this helps resolve the issue, if anything is unclear or the issue persists, feel free to reply below. 

---

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.   

Answer 2

@santosh chaturvedi

It seems that your columns C_1 to C_20 only contain zero's or positive numbers. May I suggest that you transform your data into a structured Excel table. Let's call it "Table1".

Then the following formula will filter all rows with any value in it other than zero.

=FILTER(Table1[[C_1]:[C_20]], BYROW(Table1[[C_1]:[C_20]], SUM))

Answer 3

This formula is good for google sheet.

I do not have google sheet.

How it works for normal Microsoft 365 excel sheet

Answer 4

Use a single logical test that checks all 20 columns at once and returns TRUE if any of them is > 0.

Assuming:

• The data to return is in range DATA (one row per record)
• Columns C_1 to C_20 are in the same rows as DATA and in a contiguous block, e.g. C_1 in column C and C_20 in column V

Then the FILTER formula can be written as:

=FILTER(
  DATA,
  (BYROW(C1:V147, LAMBDA(r, SUM(r)>0))),
  "N"
)

Explanation:

• C1:V147 is the block containing columns C_1 … C_20.
• BYROW(..., LAMBDA(r, SUM(r)>0)) returns TRUE for a row if the sum of all 20 columns in that row is greater than 0 (i.e., at least one non‑zero value).
• FILTER then returns only those rows from DATA where that condition is TRUE.

If BYROW is not available, use a direct SUM across the 20 columns in the filter argument, for example (adjust ranges to match DATA):

=FILTER(
  DATA,
  (C1:C147 + D1:D147 + E1:E147 + F1:F147 + G1:G147 + H1:H147 + I1:I147 + J1:J147 +
   K1:K147 + L1:L147 + M1:M147 + N1:N147 + O1:O147 + P1:P147 + Q1:Q147 + R1:R147 +
   S1:S147 + T1:T147 + U1:U147 + V1:V147) > 0,
  "N"
)

This adds the 20 columns element‑wise; any row with at least one positive value will have a sum > 0 and will be kept.

---

References:

• FILTER function