How to shorten an Excel formula?

Durdanto D August 14, 2013
Pinterest Stumbleupon Whatsapp

Can anyone help me to shorten the following formula please?
=SUM(IF(G58=”fare query”,I58=”kul”))+(IF(G59=”fare query”,I59=”kul”))+(IF(G60=”fare query”,I60=”kul”))+(IF(G61=”fare query”,I61=”kul”))+(IF(G62=”fare query”,I62=”kul”))+(IF(G63=”fare query”,I63=”kul”))+(IF(G64=”fare query”,I64=”kul”))+(IF(G65=”fare query”,I65=”kul”))+(IF(G66=”fare query”,I66=”kul”))+(IF(G67=”fare query”,I67=”kul”))+(IF(G68=”fare query”,I68=”kul”))+(IF(G69=”fare query”,I69=”kul”))+(IF(G70=”fare query”,I70=”kul”))+(IF(G71=”fare query”,I71=”kul”))+(IF(G72=”fare query”,I73=”kul”))+(IF(G74=”fare query”,I74=”kul”))+(IF(G75=”fare query”,I75=”kul”))+(IF(G76=”fare query”,I76=”kul”))+(IF(G77=”fare query”,I77=”kul”))+(IF(G78=”fare query”,I78=”kul”))+(IF(G79=”fare query”,I79=”kul”))+(IF(G80=”fare query”,I80=”kul”))+(IF(G81=”fare query”,I81=”kul”))+(IF(G82=”fare query”,I82=”kul”))+(IF(G83=”fare query”,I83=”kul”))+(IF(G84=”fare query”,I84=”kul”))+(IF(G85=”fare query”,I85=”kul”))+(IF(G86=”fare query”,I86=”kul”))+(IF(G87=”fare query”,I87=”kul”))+(IF(G88=”fare query”,I88=”kul”))+(IF(G89=”fare query”,I89=”kul”))+(IF(G90=”fare query”,I90=”kul”))

Ads by Google

  1. Bruce E
    August 16, 2013 at 12:10 am

    I'm not really sure what you are attempting to accomplish here, but wouldn't it be easier to simply drop all of the chained IF clauses inside of your SUM function and simply use the SUMIF function instead?

  2. Oron Joffe
    August 15, 2013 at 12:57 pm

    A difficult question! The way I would deal with this situation (and I think this is standard practice) is as follows:
    1. Create an intermediate value in another column (let's say, column Z), and in Z58 type =IF(G58&I58="fare querykul") .
    2. Copy that down the column to fill Z58:Z90
    3. Now revise your formula to read =SUM(Z58-90).
    fdsf
    This is not really shorter (in fact, it's probably both takes up more screeen space & RAM, and is a little slower to compute). However, it's much easier to read/understand and more flexible. Need to add a few rows? No problem, add them, and fill the Z column acordingly. Remove a few lines? Likewise.

    The reason it's so difficult to do in Excel is that, although there are various tools built into Excel to deal with things like this (array functions, SUMIF etc), they all deal with ONE value at a time (e.g. column G="fare query"), not the logical combination two values, as you have here. In the future, consider modifying the design of the spreadsheet accordingly.

  3. Jan F
    August 14, 2013 at 5:19 pm

    I don't have Excel at hand right now but my first guess would be using SUMIF
    http://office.microsoft.com/en-001/excel-help/sumif-HP005209292.aspx

    • Durdanto D
      August 19, 2013 at 6:56 am

      i JUST WANT TO KNOW A FORMULA WHERE

      CELL : A1 WILL COUNT DOWN (1+1+1)
      VALUE "1" = SELECTION OF "fare query" "kul" FROM DROP DOWN LIST

      CELL : B1 COUNT DOWN (1+1+1)
      VALUE "1" = SELECTION OF "pnr" "kul" FROM DROP DOWN LIST

      CELL : A2 COUNT DOWN (1+1+1)
      VALUE "1" = SELECTION OF "fare query" "bki" FROM DROP DOWN LIST

      HOPE I HAVE CLEARED MY QUESTION