+ Reply to Thread
Results 1 to 3 of 3

Add Condition For if and sumif Function Formula

  1. #1
    Registered User
    Join Date
    01-27-2009
    Location
    Owen Sound, Ontario
    MS-Off Ver
    Excel 2016
    Posts
    59

    Smile Add Condition For if and sumif Function Formula

    I am hoping to some help with a formula. I use the attached spreadsheet to track professional development within our organization. I know it would be much easier to create a Access database for all this spreadsheet is doing but for reasons I don’t quite understand we do not have Access available to us. So I created this spreadsheet last year so we are able to track and analyze our professional development for staff in 1 centralized location. The spreadsheet is also used to summarize the budget. I have a tab called Budget Summary that refers to the tab that the data is collected. Rather than create a new spreadsheet for 2015 I think it would be much to add a Budget summary tab for each year and continue to collect the data in the same spreadsheet so I am able to look at data through pivot tables over the years. My problem is that I did not not think of this last year when I created the spreadsheet. The formulas I created do not allow for a separation of year. I have added a column on the Data tab for “Year of Event” and I have to create formulas that will let me add another condition that will separate the year. On the Budget Summary tab I have a “Reimbursed Summary” and a “Budget Code Summary” and I am struggling to come up with an adjustment to the current formulas or a new formula that will do this. Formulas in both summaries are doing quite a bit already so I am no sure if it is even possible to keep everything they are doing now and still be able to separate the year. My plan would be to have a Budget Summary tab for each year. If anyone is able to help it would make this spreadsheet so much more functional for me. I have deleted a lot rows from the Data tab. We have a very large number of events for our organization that this spreadsheet tracks. I have also removed employee names and info. I think you can tell by reading the formulas what exactly they are doing but I can clarify if needed.

    Thanks s much in advance to anyone even willing to try
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    04-12-2012
    Location
    India
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, 2019, Office 365, Google Spreadhsheet
    Posts
    640

    Re: Add Condition For if and sumif Function Formula

    Hi Squint,

    I have attached a modified report for your reference and have incorporated following modification to the report.

    1. Removed lookup formula to pickup "MMMM-YY" in column C in Date tab and used text function which will give you the same result you were looking for.
    2. Simply used the year function in Column D of Data tab.
    3. Assuming, you would only wanted to see the data for particular any year or All in Budget Summary sheet so deleted all the unwanted rows and made the dynamic summary in just 4-5 rows to give you the same what you looking for.
    3.1. I have included "Year" option to select for Reimbursed summary and Budget Code Summary in "Budget Summary" sheet tab. e.g. If you want to see All data sum, then select All else select any particular year to see the total value of it.
    3.2. "Budget Code Summary" now have the option to enter any Budget Code and you can see the data for the same in next columns.
    3.3. Black Background coded color is showing the grand total in "Budget Summary" sheet.

    I hope you will like it.

    Waiting for your feedback. Please revert in case of any query.

    Thanks,
    Anil Dhawan
    Attached Files Attached Files
    Thanks,
    Anil Dhawan


    Click *Add Reputation to say "Thanks" and don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved) if you are satisfied.

    Don't stop when you are tired. STOP when you are done!

  3. #3
    Registered User
    Join Date
    01-27-2009
    Location
    Owen Sound, Ontario
    MS-Off Ver
    Excel 2016
    Posts
    59

    Re: Add Condition For if and sumif Function Formula

    Just amazing. Wow. I don't know how to thank you. You made so many amazing improvements to the spreadsheet. I have applied it all to the current spreadsheet with all of the data and everything works perfectly. I like the way you have set up the budget summary page much more then the way I had set it up. I did not like all of the rows showing under the Reimbursed Summary but did not know another way of doing it. Same with the Budget Code Summary. I love that finance can now just populate the budget code. You are amazing and I can not thank you enough. Now I just want to analyze the formulas if I can and see if I can figure out how you did it.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Unable to use a column with dates as a condition for a sumif function
    By EWool in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 07-30-2013, 09:17 AM
  2. If and sumif condition formula
    By santanuKD in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-18-2011, 03:27 AM
  3. Sumif function condition cell value
    By borut_p in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-24-2009, 03:02 PM
  4. Multiple Condition SumIf Formula
    By JP7 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-19-2007, 09:57 PM
  5. SUMIF function with text color condition
    By MrWallyWorld in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-17-2006, 11:10 PM

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