• 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...

  • 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

ahmetk

Member
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.
 

Attachments

  • Expensense Distribution.xlsx
    25 KB · Views: 7
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
 

Attachments

  • Expensense Distribution(1).xlsx
    18.4 KB · Views: 7
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:)
 

Attachments

  • Copy of Expensense Distribution.xlsx
    19.3 KB · Views: 5
Back
Top