Ms Excel Formula Help Required for Sum of Numbers Related to Specific EOFY Year

No ONE on 09/08/2015 - 16:19

Hello everyone,

Nothing interesting or related to bargain, but money matters that requires calculation. Here is my hypothetical scenario.

lets say, I have a bank statement (excel format) that has huge lists of transaction from the past 5 years. It includes both debits and credits. Here is a hypothetical columns/titles in excel.

- A_________B___________C______________D__________E

1…DATE……..DESCP……………..AMOUNT……………TOTALS
2
3
4
5
6
7

The date starts from the last 5 years continuously till present. What I want is that I would like to sum the totals in another sheet that reflects to the specific end of the financial years. Lets says, sum of all expense & income in 2013-2014 and exclude amounts that are not related to 2013-2014 years.

The rule is
* I cannot manually calculate for specific year,
* I do not want to separate the data for each year
* I do not want to use excel filters/sort options
* I do not want to manually select the cell ranges (e.g. D2:D5) to do calculation
* Not manually using Cell range (e.g. A5:A30) for Column A (date column) in a formula

What I want is a formula that should automatically Calculate from Column D in reference to specific date from column A and exclude the amount that is not related to specific year (EOFY).

Financial

Comments

  • RockyRaccoon on 09/08/2015 - 17:07
    +1

    You can use the sumif function

    Rather than explain read this thread it will give you the examples on dates that I think you are looking for.
    Read all the posts as there seems to be a number of ways including the OP's which was corrected by another poster

    http://www.mrexcel.com/forum/excel-questions/601474-sumifs-d…

  • trex on 09/08/2015 - 19:02
    +1

    As per RockyRaccoon, Sumif is a good start. Other alternative includes:-

    1. Under Column E (currently blank), insert IF function to return YYYY based on column A's date where 1 July 2013 to 30 June 2014 will return 2014. Once done, use a simpler SUMIF function to subtotal 2014.

    2. Use the Pivot table function. If column A is set correctly where Excel recognizes it as a date field, Pivot will allow you to select the date range from 1 July 2013 to 30 June 2014.

    • dasher86 on 09/08/2015 - 20:22

      I'd just pivot it myself.

  • herculepoirot on 10/08/2015 - 00:34
    +2

    You can use the SUMIFS function as well.
    For example, you can use Column F & G to specify the start & end date for the financial year & then use the SUMIFS formula in Column H as below:

    ___F_______________G____________H
    FinYrStart…FinYrEnd….FinYrTotal
    1/7/13…….30/6/14…..=SUMIFS(D:D,A:A,">="&F2,A:A,"<="&G2)
    1/7/14…….30/6/15…..=SUMIFS(D:D,A:A,">="&F3,A:A,"<="&G3)

    and so on…

    • RockyRaccoon on 10/08/2015 - 05:39

      Good one. Never knew that variation existed. Thanks

Login or Join to leave a comment
Login Join