# Classifying expenses

#### Tigerexcel

##### Active Member
Classifying expenses.xlsx
ABCDEFGH
1AmountDetailsTransfer outTransfer inRentTaxRatesWater
2-\$1,500Transfer to other Bank Transfer out
3\$1,000Transfer from BankTransfer in
4\$12,000Water CityWater City
5\$500Rent SuburbRent Suburb
6\$3,000Rent CityRent City
7\$6,000Rates CityRates City
8\$3,000Rates invoice CityRates City
9\$500Tax invoice SuburbTax Suburb
10
11
12SummaryBalance
13Transfer out\$1,500
14Transfer in\$1,000
15Rent Suburb\$500
16Rent City\$3,000
17Rates City\$9,000
18Water City\$12,000
Sheet1

Desired output is in C2 to H9. Ultimately would like to have a summary as shown. The problem I'm running into is that the Detail field can vary but I'd need formulae/functions to pick up variations such as Rates City or Rates Invoice City. The details do come in an inconsistent manner so could also have something like Invoice 234 Rates (Jan to Mar) City.

Last edited:

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

##### Well-known Member
Try this:
Book1
ABCDEFGHI
1AmountDetailsTransfer outTransfer inRentTaxRatesWater
2(\$1,500)Transfer to other BankTransfer out
3\$1,000Transfer from BankTransfer in
4\$12,000Water CityWater City
5\$500Rent SuburbRent Suburb
6\$3,000Rent CityRent City
7\$6,000Rates CityRates City
8\$3,000Rates invoice CityRates City
9\$500Tax invoice SuburbTax Suburb
10
11
12SummaryBalance
13Transfer out\$1,5001500
14Transfer in\$1,0001000
15Rent Suburb\$500500
16Rent City\$3,0003000
17Rates City\$9,0009000
18Water City\$12,00012000
19
Sheet1
Cell Formulas
RangeFormula
E13:E18E13=ABS(SUMPRODUCT((\$C\$2:\$H\$9=B13)*(\$A\$2:\$A\$9)))

#### Tigerexcel

##### Active Member
Thanks maabadi, if possible I need formulae to fill in C2 to H9.

##### Well-known Member
You want to numbers At range C2:H9, I thinks you want Extract summary based them.
Try this:
Example Sheet.xlsx
ABCDEFGHI
1AmountDetailsTransfer outTransfer inRentTaxRatesWater
2-1500Transfer to other Bank\$ (1,500.00)
31000Transfer from Bank \$1,000.00
412000Water City \$12,000.00
5500Rent Suburb \$ 500.00
63000Rent City \$ 3,000.00
76000Rates City \$6,000.00
83000Rates invoice City \$3,000.00
9500Tax invoice Suburb \$ 500.00
10
Sheet1
Cell Formulas
RangeFormula
C2:H9C2=IF(COLUMN()-2=MATCH(IF(LEFT(\$B2,FIND(" ",\$B2)-1)="transfer",LEFT(\$B2,FIND(" ",\$B2,FIND(" ",\$B2)+2)-1),LEFT(\$B2,FIND(" ",\$B2)-1)),CHOOSE({1,2,3,4,5,6},"Transfer to","transfer from","rent","tax","rates","Water"),0),\$A2,"")

#### Tigerexcel

##### Active Member
You want to numbers At range C2:H9, I thinks you want Extract summary based them.
Try this:
Example Sheet.xlsx
ABCDEFGHI
1AmountDetailsTransfer outTransfer inRentTaxRatesWater
2-1500Transfer to other Bank\$ (1,500.00)
31000Transfer from Bank \$1,000.00
412000Water City \$12,000.00
5500Rent Suburb \$ 500.00
63000Rent City \$ 3,000.00
76000Rates City \$6,000.00
83000Rates invoice City \$3,000.00
9500Tax invoice Suburb \$ 500.00
10
Sheet1
Cell Formulas
RangeFormula
C2:H9C2=IF(COLUMN()-2=MATCH(IF(LEFT(\$B2,FIND(" ",\$B2)-1)="transfer",LEFT(\$B2,FIND(" ",\$B2,FIND(" ",\$B2)+2)-1),LEFT(\$B2,FIND(" ",\$B2)-1)),CHOOSE({1,2,3,4,5,6},"Transfer to","transfer from","rent","tax","rates","Water"),0),\$A2,"")
Thanks Maabadi, I will give this a try, much appreciated.

1,129,677
Messages
5,637,735
Members
416,981
Latest member
PLonchar

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back