Add addtional formulation in FILTER formula

Question

Add addtional formulation in FILTER formula

Chaturvedi, Santosh 340

I filter the data by this formula

=FILTER('[Fatigue Assesment_as per 17949_Welded Joints.xlsm]S1'!$E$1466:$F$2065,

(BYROW('[Fatigue Assesment_as per 17949_Welded Joints.xlsm]S1'!$E$1466:$E$2065,

LAMBDA(r, SUM(r)>0))),"N")

I want to delete the "-" value from source table in above filter formula.

Then I want to multiply particular cell values in whole column "say by 120" --

I want to SORT the values from "large t o low"

Can you please add these features in above filter formula. ?

Please advice.

0 comments

3 answers

Your answer

Answer 1

Hello

Based on the formula you shared:

=FILTER('[Fatigue Assesment_as per 17949_Welded Joints.xlsm]S1'!$E$1466:$F$2065,

(BYROW(

'[Fatigue Assesment_as per 17949_Welded Joints.xlsm]S1'!$E$1466:$E$2065,

LAMBDA(r,SUM(r)>0))),"N")

My understanding is that the formula currently:

Returns data from columns E:F.
Uses BYROW + LAMBDA to evaluate each row based on the value in column E.
Only keeps rows where the value (or row total) is greater than 0.
Returns "N" if no matching records are found.

Regarding the additional enhancements you would like to add:

1/ Remove the "-" value from the source table

This requirement is not completely clear to me yet because the meaning of "-" could vary:

1.1> If the "-" in your source data is a text value and you would like to exclude those rows, you may be able to extend your existing FILTER criteria as follows:

=SORT(FILTER('[Fatigue Assesment_as per 17949_Welded Joints.xlsm]S1'!$E$1466:$F$2065,(BYROW('[Fatigue Assesment_as per 17949_Welded Joints.xlsm]S1'!$E$1466:$E$2065,LAMBDA(r,SUM(r)>0)))*('[Fatigue Assesment_as per 17949_Welded Joints.xlsm]S1'!$E$1466:$E$2065<>"-"),"N"),1,-1)
The additional portion is: *('[Fatigue Assesment_as per 17949_Welded Joints.xlsm]S1'!$E$1466:$E$2*65<>"-")which excludes rows where column E contains the text value "-".

1.2> If "-" actually represents negative numbers and you want to exclude them, the condition could instead be:

=FILTER('[Fatigue Assesment_as per 17949_Welded Joints.xlsm]S1'!$E$1466:$F$2065,(BYROW('[Fatigue Assesment_as per 17949_Welded Joints.xlsm]S1'!$E$1466:$E$2065,LAMBDA(r,SUM(r)>0)))*('[Fatigue Assesment_as per 17949_Welded Joints.xlsm]S1'!$E$1466:$E$2065>=0),"N")

Added portion:

*('[Fatigue Assesment_as per 17949_Welded Joints.xlsm]S1'!$E$1466:$E$2065>=0)

Could you clarify which of the above scenarios matches your requirement?

2/ Multiply values by 120

If you want to multiply all values returned by the FILTER formula by 120, you can add: *120

For example:

=FILTER('[Fatigue Assesment_as per 17949_Welded Joints.xlsm]S1'!$E$1466:$F$2065,(BYROW('[Fatigue Assesment_as per 17949_Welded Joints.xlsm]S1'!$E$1466:$E$2065,LAMBDA(r,SUM(r)>0))),"N")*120

If you only want to multiply a specific column (for example, column E) while leaving the other returned column unchanged, the formula would need to be adjusted so that the multiplication applies only to that target column.

3/ Sort from largest to smallest

This can be done by wrapping the filtered output with SORT().

For example, if you would like to sort by the first column of the filtered result in descending order:

=SORT(FILTER('[Fat*gue Assesment_as per 17949_Weld*d Joints.xlsm]S1'!$E$1466*$F$2065,(BYROW('[Fatigue Assesment*as per 17949_Weld*d Joints.xlsm]S1'!$E$1466*$E$2065,LAMBDA(r,SUM(r)>0))),*N"),1,-1)

Added portion:

*SORT( existing_formula ,1,-*)

Where:

1 = sort by the first column.
-1 = descending order (largest to smallest).

Alina Le 4,920 Reputation points Independent Advisor

2026-09-10T15:54:45.72+00:00

Hi,

Would you like me to continue working through this with you?

Please let me know if you'd like any further assistance. I look forward to your update.

Answer 2

Chaturvedi, Santosh 340

Hello - The below folmula is working well

=SORT(

FILTER(

    HSTACK(

        '[Fatigue Assesment_as per 17949_Welded Joints.xlsm]S1'!$E$1466:$E$2065,

        '[Fatigue Assesment_as per 17949_Welded Joints.xlsm]S1'!$F$1466:$F$2065*$I$3        ),

    ('[Fatigue Assesment_as per 17949_Welded Joints.xlsm]S1'!$E$1466:$E$2065<>"-")*

    (BYROW('[Fatigue Assesment_as per 17949_Welded Joints.xlsm]S1'!$E$1466:$E$2065,LAMBDA(r,SUM(r)>0))),

    "N"

),

2,

-1

)

But

i want to sort values from "high to Low" values according to values which are in cloumn 2 (ni(⊥)) .

User's image

Please add and update eh formula above as required.

Barry Schwarz 6,106 Reputation points

2026-09-09T19:18:39.3566667+00:00

The formula you are using sorts columns E:F in descending order of F. E and F are parallel columns. How do they relate to columns 1 and 2?

It would help considerably if you showed us sample data and desired result.
Chaturvedi, Santosh 340 Reputation points

2026-09-10T06:33:09.54+00:00

Hello Barry -- I have tried my self and i reformulated teh filter formula. This is working well.

Thanks for your help in this regard..
Rory Archibald 19,055 Reputation points Volunteer Moderator

2026-09-10T08:24:39.1866667+00:00

FYI, adding this criterion:

('[Fatigue Assesment_as per 17949_Welded Joints.xlsm]S1'!$E$1466:$E$2065<>"-")

serves no purpose. The SUM filter would already exclude those since the sum would be 0.
Chaturvedi, Santosh 340 Reputation points

2026-09-10T08:49:57.13+00:00

Hello Rory -- I deleted the string. It is working well again without this. Thanks for this updates..

Answer 3

Use SORT around FILTER, and add the extra conditions inside the include argument.

If column E contains the values to test, and column F is the column to multiply by 120, this pattern does all three things:

=SORT(
    FILTER(
        HSTACK(
            '[Fatigue Assesment_as per 17949_Welded Joints.xlsm]S1'!$E$1466:$E$2065,
            '[Fatigue Assesment_as per 17949_Welded Joints.xlsm]S1'!$F$1466:$F$2065*120
        ),
        ('[Fatigue Assesment_as per 17949_Welded Joints.xlsm]S1'!$E$1466:$E$2065<>"-")*
        (BYROW('[Fatigue Assesment_as per 17949_Welded Joints.xlsm]S1'!$E$1466:$E$2065,LAMBDA(r,SUM(r)>0))),
        "N"
    ),
    2,
    -1
)

What this does:

('[...]E1466:E2065<>"-") removes rows where column E is -.
HSTACK(..., F*120) returns:
- column E as-is
- column F multiplied by 120
SORT(...,2,-1) sorts by the 2nd returned column in descending order, so values go from large to low.

If both returned columns also need to be multiplied by 120, use this instead:

=SORT(
    FILTER(
        '[Fatigue Assesment_as per 17949_Welded Joints.xlsm]S1'!$E$1466:$F$2065*120,
        ('[Fatigue Assesment_as per 17949_Welded Joints.xlsm]S1'!$E$1466:$E$2065<>"-")*
        (BYROW('[Fatigue Assesment_as per 17949_Welded Joints.xlsm]S1'!$E$1466:$E$2065,LAMBDA(r,SUM(r)>0))),
        "N"
    ),
    2,
    -1
)

If - is in column F instead of E, change the condition to:

'[Fatigue Assesment_as per 17949_Welded Joints.xlsm]S1'!$F$1466:$F$2065<>"-"

For multiple conditions in FILTER, Excel uses Boolean arrays, and examples use * for AND conditions. SORT(FILTER(...),..., -1) is also the documented pattern for filtering and then sorting descending.