+ Reply to Thread
Results 1 to 8 of 8

Complex formula that incorporates If/Then

  1. #1
    Registered User
    Join Date
    06-14-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    9

    Complex formula that incorporates If/Then

    So, I have a workbook that contains several spreadsheets in it. There is an individual sheet for each of about 13 individual accounts. All the information input into each individual sheet then links to a shared sheet. On the individual sheets, I have created drop down menus that select the month an expense is incurred and the type of expense(category). The If/Then formula I received on this forum once before is as follows: =SUMIFS('AUSTRALIA 60-3-0001'!$D$4:$D$1048576,'AUSTRALIA 60-3-0001'!$B$4:$B$1048576,C$5,'AUSTRALIA 60-3-0001'!$C$4:$C$1048576,$B6)

    Australia 60-3-0001 refers the the individual account sheet being reference into the shared sheet. The formula works fine, however there is an expense category named Overhead. In the individual account sheet I have set up where a 22% overhead expense is generated for each expense entered, yet it is not one of the categories selected in the drop down menu. There is a corresponding category field in the shared sheet that I am trying to have populated with a running total of overhead expenses for each selected month. How do I create a formula to do this?

    I have attached the actual workbook here.AP accounts expense worksheet.xlsx

  2. #2
    Forum Contributor
    Join Date
    02-28-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    272

    Re: Complex formula that incorporates If/Then

    Need more clarity on this
    Please consider adding a * if I helped

  3. #3
    Registered User
    Join Date
    06-14-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Complex formula that incorporates If/Then

    So, in the individual account sheet, I will choose 'Australia 60-3-0001' as an example. Now, in the sheet I enter an expense, starting with selecting what month the expense is incurred with a drop down menu. Then I select what expense category. after that, I hand enter the numerical amount of the expense. Directly to the right in the next column after the expense amount I have created a column that automatically calculates a 22% numerical amount based on what expense amount I entered. So, on the shared spreadsheet named 'ACTUAL EXPENSES' there is an expense category called 'Overhead - 22%: 30-6010' at the bottom of each account table, in this case the first table called 'Australia 60-3-0001'
    This expense category is NOT one of the expense categories in the drop down menu on the individual account sheet (Australia 60-3-0001)
    What I need is to have whatever amount is generated in the 'Overhead 22%' column on the the individual account sheet (Australia 60-3-0001) to be populated in the 'ACTUAL EXPENSES' sheet under the correct month. In addition, if there are 4 expenses in one month, say October, then I need an accumulated amount to show up, that way I can get an accurate total of monthly expenses incurred included the 22% overhead in the total boxes under the table of each account.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,939

    Re: Complex formula that incorporates If/Then

    Where is this (22%) total loacted, and could you not just take the total from the summary sheet and apply 22% to it, rather than summing from each data sheet?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Forum Contributor
    Join Date
    02-28-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    272

    Re: Complex formula that incorporates If/Then

    In "Total Expenses" sheet against Overhead -22%: 30-6010 put following function:

    Please Login or Register  to view this content.
    and also in each individual sheet there is an error like under 0.22 you need to put $ between E3 like E$3 to freeze 0.22 cell

  6. #6
    Registered User
    Join Date
    06-14-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Complex formula that incorporates If/Then

    I actually ended up deleted the overhead total column from the individual account sheets (column G) because it did not differentiate by month.
    WFM007; I used your formula in the 'ACTUAL EXPENSES' sheet, on the 'Overhead -22%: 30-6010' row under the Oct column and then dragged the cell across to apply it to the entire row. What I noticed was that it also changed the formula for the entire Oct column above it, but not for the other months. What I assume is that any expenses entered into the individual sheets with October chosen as the month will no function correctly. Would I need to isolate the 'Overhead -22%: 30-6010' expense category and place it instead in the table below the blue bar called "Total Expenses" or is there a way to have one formula for the entire table but a different formula for just the 'Overhead -22%: 30-6010'?

    The formula seemed to work with the rest of the table and calculated the overhead expenses as an accumulated total in the months that I started to enter expense amounts, but again my concern is that the formula for the October column changed.
    I have attached an updated workbook with your formula added:2013 AP accounts expense worksheet.xlsx

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,939

    Re: Complex formula that incorporates If/Then

    Take a look at the attached. I added a helper Q, you can hide this if you want.

    I used that helper to modify the formula in each table so thyat you dont need to keep adjusting the formula for each table/sheet, and also included a component that will pull in the 22%

    let me know if this is something you can work with?
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    06-14-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Complex formula that incorporates If/Then

    wfm007 Thank you for the tip on the putting $ between E3 like E$3 to freeze 0.22 cell
    Your formula that you gave me above works

    FDibbins: Thank you for you helping. I very much appreciate it.
    Each expense that I enter into the individual account sheet ( in my example; AUSTRALIA 60-3-0001) The 22% overhead is calculated per individual expense and not as a summed total and separated by month. Thus why this is so complicated.

    After much work and tinkering, I was able to figure out and correct my problem. Thank you both for all of your assistance

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1