SUMIF calculate sum with percentage from another cell

[the_ausswe]

Hi!
I need help to solve a SUMIF (or similar formula) to calculate expenses based on percentage.

In Sheet1 I have the following columns (defined as names):
- Amt_ex_GST (total amount for the expense)
- Type_expense (text based value, type of expense)
- Location1 (percentage, how much of the above amount that should be allocated to this location)
- Location2 (percentage, how much of the above amount that should be allocated to this location)
- Location3 (percentage, how much of the above amount that should be allocated to this location)
- Location4 (percentage, how much of the above amount that should be allocated to this location)

In Sheet2 I have lined up the different expense types in column A and I have allocated B:E for Location1 through to Location4.
So I'm trying to calculate the total amount for the different expenses based on the percentage for each location, I have tried the following but wasn't successful:

=SUMIF(Type_expense,A2,(Amt_ex_GST*Location1)

Anyone who can give me some ideas?

Thanks!

[Debraj Roy]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[Richard Buttrey]

Hi, and welcome to the forum

If you''d care to upload the workbook or at least the relevant bit of it and manually add the results you expect to see and noting which cells are the result cells then no doubt we will be able to help further.

[the_ausswe]

Thanks for your quick response!

Attached is a sample file - hope it makes sense
Sample.xlsx

[Richard Buttrey]

Hi,

You have two Bank Charges rows on the Data sheet. How is the Report sheet to know which one to use. In addition you have two different multiplying factors in the Report sheet, 100 & 50. How many more are there for other types of cost?

[the_ausswe]

Hi Richard.
The first sheet 'Data' contains a number of transactions, in this case pulled from bank statements.
Each expense is then given the 'Type of expense' and each location is seen as a 'cost cut' to which the expense is allocated either 100% of the cost or a different percentage.

So what I'm trying to achieve is for the Report sheet to line all types of expenses and then the accumulated cost so far based on the transactions and the 'cost cut' for each location.

[Richard Buttrey]

Hi,

In B2 copied across and down

Please Login or Register  to view this content.

Change A1:A16 in the formula as appropriate. better still create a dynamic range name for your data and use the range name in the formula.

[the_ausswe]

Hi Richard.
Sorry for late reply but haven't had time to look back at this.

It seems like the formula is not calculating the values correctly.
I can't get my head around where in the formula it takes into account the amounts in 'Amt_ex_GST'?

Attached is an updated Sample file and I have added a few more records in the Data sheet.

Thanks
Sample2.xlsx

[JR DHONA]

hi..

I think you can use "sumproduct"..

please check it.

Regards,

[the_ausswe]

Bingo!
That one worked - thank you!