Is there a way to shorten the excel formula?-CodePudding

I'm looking for a way to shorten my formula. I'm new to excel and have no idea how I could shorten the formula, I've made several attempts but always get errors. I'm sure for someone more experienced it will be a trivial question.

I hope someone can help me, I appreciate any response.

=SUM(IF((ISNUMBER(SEARCH("Bench",$P$9:$U$11))) (ISNUMBER(SEARCH("Press",$P$9:$U$11)));V9:V11*$W$9:$W$11,0)) SUM(IF((ISNUMBER(SEARCH("Bench",$P$19:$U$21))) (ISNUMBER(SEARCH("Press ";$P$19:$U$21)));V19:V21*$W$19:$W$21;0)) SUM(IF((ISNUMBER(SEARCH("Bench",$P$29:$U$31))) (ISNUMBER(SEARCH("Press",$P$29:$U$31)));V29:V31*$W$29:$W$31,0)) SUM(IF( (ISNUMBER(SEARCH("Bench",$P$39:$U$41))) (ISNUMBER(SEARCH("Press",$P$39:$U$41)));V39:V41*$W$39:$ W$41;0))

CodePudding user response：

I don't think there's any "easy" savings here. What I do is I "decompose" a wildly complex formula in a way that helps me visualize the logic better. In this case:

=SUM( IF( 
    (ISNUMBER( SEARCH( "Bench" , $P$9:$U$11 ) ) )  
    (ISNUMBER( SEARCH( "Press" , $P$9:$U$11 ) ) ) ;
    V9:V11 * $W$9:$W$11 , 
    0 ) )

 SUM( IF(
    (ISNUMBER( SEARCH( "Bench" , $P$19:$U$21 ) ) )  
    (ISNUMBER( SEARCH( "Press" ; $P$19:$U$21 ) ) ) ;
    V19:V21 * $W$19:$W$21 ;
    0 ) )

 SUM( IF(
    (ISNUMBER( SEARCH( "Bench" , $P$29:$U$31 ) ) )  
    (ISNUMBER( SEARCH( "Press" , $P$29:$U$31 ) ) ) ;
    V29:V31 * $W$29:$W$31 ,
    0 ) )

 SUM( IF(
    (ISNUMBER( SEARCH( "Bench" , $P$39:$U$41 ) ) )  
    (ISNUMBER( SEARCH( "Press" , $P$39:$U$41 ) ) ) ;
    V39:V41 * $W$39:$W$41;
    0 ) )

I didn't change anything except to format it in a way that lets me see the pattern... And I want to work on patterns, not on specifics.

What I can see is this formula searches 4 ranges of 6r x 3c ranges for any instance of either "Bench" or "Press". If there is at least one instance, multiply 2 3-cell arrays and sum the result, otherwise return zero for that range.

You have O365, so you can create a named LAMBDA() function in your name manager. For instance you could create a custom Excel function in the name manager like:

=snorlaxFunction( "Bench", "Press", $P$39:$U$41 )

which would simplify each of the 4 terms to:

 SUM( IF( snorlaxFunction( "Bench", "Press", $P$39:$U$41 ), 
    V39:V41 * $W$39:$W$41; 0 ) )

You could go step further and encapsulate the whole SUM(IF()) to be a function:

=snorlaxFunction( "Bench", "Press", $P$39:$U$41, V39:V41, $W$39:$W$41 )

In which case your WHOLE formula becomes:

= snorlaxFunction( "Bench", "Press", $P$9:$U$11 , V9:V11 , $W$9:$W$11  )  
  snorlaxFunction( "Bench", "Press", $P$19:$U$21, V19:V21, $W$19:$W$21 )  
  snorlaxFunction( "Bench", "Press", $P$29:$U$31, V29:V31, $W$29:$W$31 )  
  snorlaxFunction( "Bench", "Press", $P$39:$U$41, V39:V41, $W$39:$W$41 )

Honestly, if it was me... I don't see any easy way to shorten the logic, and I don't have repeated terms that are good candidates for a LET() function. So I'd leave the formula as-is but use alt enters to present the formula in outline format like I did above. I wouldn't go through the work to create named LAMBDA()s for this unless I was going to be reusing the formula many times in the spreadsheet.

NOTE

One thing I noticed is your notation drifts a lot between comma delimited and semicolon delimited text, as you can see above. I would stick to your configured regional setting and be 100% consistent, using only commas or semicolons.

CodePudding user response：

Use SUMPRODUCT:

=SUMPRODUCT(
    (ISNUMBER(MATCH(MODE(ROW($P$9:$U$41),10);{9;0;1};0)))*
    ((ISNUMBER(SEARCH("Bench";$P$9:$U$41))) 
    (ISNUMBER(SEARCH("Press";$P$9:$U$41))));
    $V$9:$V$41;
    $W$9:$W$41)

This allows the full range to be entered and it will only look at those rows that end in 9,0, or 1.

CodePudding user response：

Using LET and stacking the ranges you can simplify your formula:

=LET(pu,VSTACK($P$9:$U$11,$P$19:$U$21,$P$29:$U$31,$P$39:$U$41),
     v, VSTACK(V9:V11,V19:V21,V29:V31,V39:V41),
     w, VSTACK($W$9:$W$11,$W$19:$W$21,$W$29:$W$31,$W$39:$W$41),

SUM(IF(
       (ISNUMBER(SEARCH("Bench",pu))) 
       (ISNUMBER(SEARCH("Press",pu))),
       v*w,
       0)))

And as suggested by Scott Craner you could start with the range covering all columns/rows and filter out the ranges to be used. That way, if data may grow, you only need to adjust the first LET condition to have the formula adjusted:

=LET(rng,P9:W41,
     pw,FILTER(rng,ISNUMBER(XMATCH(MOD(ROW(rng),10),{9;0;1}))),
     pu,TAKE(pw,,COLUMNS(pw)-2),
     v,  CHOOSECOLS(pw,COLUMNS(pw)-1),
     w, TAKE(pw,,-1),
SUM(IF(
       (ISNUMBER(SEARCH("bench",pu)))  
       (ISNUMBER(SEARCH("press",pu))),
       v*w,
       0)))