1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Expense Distribution based on different data with in descriptions

Discussion in 'Ask an Excel Question' started by ahmetk, Sep 14, 2018.

  1. ahmetk

    ahmetk New Member

    Messages:
    28
    Dear Excel Gurus,

    I get a list that I need to sort and prepare a list of expenses every month for our finance deparment.

    The data comes from our accounting deparment to me end of every month and the titles, desription, etc... are always same. (Please see the attached file, the date shown under the tab "ergosis" from A1 to G56 (may grow depending on the number of cars used) comes standard every month, only the amounts change based on the spendings)

    The finance deparment demands to see a summarized expense table sorted based on expense type (HGS (otomatic pass system), Kira (rent) and Yakıt (gas)) and departmant (Operation, Sales, Technical and Software).

    I also have a list showing me which license plate (car) belongs to which deparment in order for me distribute their expenses.

    I distribute all expenses based on the license plate number and to the department that license plate belongs to. Under the desciption part of the list, all details are mentioned; the license plate number, whether it is HGS, Kira and Yakıt. These words always appear in the description part of the list.

    Question; Is there a way (not a macro) formula that I can use (write), so that each month when this data comes, instead of checking each description and add the values one by one, it automatically checks the license plate number and the expense type and based on the license plate and the related department, calculates the sums automatically?

    I tried SUMIF formulas but couldn't do it. The license plates data are saved differently for different expense types, for instance for "Kira" it is "34 SK 0324", the same license plate turns "34SK0324" for YAKITi not only that there could be more than 1 YAKIT, Kira and /or HGS for the same plate in the list.

    Kind regards.

    Ahmet K.

    Attached Files:

  2. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    1,909
    Try this formula with helper solution.

    1] In Helper1,"Dept name" I2. formula copied down :

    =LOOKUP(1,-SEARCH(C$59:C$68,SUBSTITUTE(F2," ","")),B$59:B$68)

    2] In Helper2 "Expense type" J2, formula copied down :

    =LOOKUP(1,-SEARCH($B$79:$D$79,F2),$B$79:$D$79)

    3] In "Summary table" B80, copied across to D80 and all copied down :

    =SUMPRODUCT(($I$2:$I$56=$A80)*($J$2:$J$56=B$79)*$G$2:$G$56)

    4] In "Summary table" E80, copied down :

    =SUM(B80:D80)

    Regards
    Bosco

    Attached Files:

  3. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    781
    Perhaps with some helper columns in the table
    [H2]=UPPER(SUBSTITUTE([@[Descp.]]," ",""))
    [H3]= IF(NOT(ISERROR(FIND("HGS",[@[Normalize Descp.]]))),"HGS",IF(NOT(ISERROR(FIND("KIRA",[@[Normalize Descp.]]))),"KIRA",IF(NOT(ISERROR(FIND("YAKIT",[@[Normalize Descp.]]))),"YAKIT","No Type Defined")))
    [H4]=INDEX($B$59:$B$68,MATCH(1,ISNUMBER(SEARCH("*"&$C$59:$C$68&"*",[@[Normalize Descp.]]))*1,0))

    The formula in [H4] is entered as array formula, confirmed with CTRL + SHIFT + ENTER.

    In the range =B72:D75 use a sumifs.

    EDIT: for the second time today, beaten by Bosco:)

    Attached Files:

  4. ahmetk

    ahmetk New Member

    Messages:
    28
    Dear Bosco,

    Thank you very much, you are life saver :)

    Best regards.

    AK.

Share This Page