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