YTD calculations with multiple criteria

    I need to calculate the YTD total based on a month input (cell A1 in the example). In addition, the summation is also based on several criteria. Attached is a sample of the data table. What is the formula to get the YTD Actuals of "OTHER" expenses? The YTD May total should be 191,420.


    Many Thanks.

    Hello and Welcome to the Forum :)


    Attaching your sample file would make everything easier ...


    Bear in mind an image is a totally inert object ...!!!

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    This may or may not help, but I would suggest making 2 rows titled "Total" and "YTD" at the bottom of each column. use =SUM in the total field and reference the rows above it. Then in YTD use SUM (or just simply add) the previous month to the month following to create your YTD. Then use INDEX Match to index the YTD row and Match P01 - P##. I created a sample and attached it in case I didn't explain correctly:

    Hi TBro, Thanks very much for your suggested answers. I have updated the data table with more rows and the end product layout on the right (shaded grey). Your suggestion would work if it only involves one group of expense category. Imagine I need to summarize the data into an income statement format every month. I am hoping there is a formula that can dynamically calculate the total based on the defined parameters. Any other thoughts or suggestions? Appreciate anyone's help.

    Hello,


    You seem to be after some kind of consolidation ...


    Sadly you have not filled in (even manually ...) your shaded grey area ...


    Have you tried to Insert a Pivot Table ...???

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login