# Formula to Sum by Month and Financial Year

1. ## Formula to Sum by Month and Financial Year

I have a spreadsheet that consists of date, product number with amount received against each date.

What I need are formulas that will automatically provide me with:
1. The sub-total monthly income for each product against each financial year.
2. Total income for each product by financial year.
3. Total income for each financial year.

I have attached the spreadsheet with an example. Hope someone can help. Thanks

2. ## Re: Formula to Sum by Month and Financial Year

Which is your financial Year, Jan to Dec or April to Mar

3. ## Re: Formula to Sum by Month and Financial Year

see the attached file

4. ## Re: Formula to Sum by Month and Financial Year

Hi this is set up for the UK financial year. Easy to reset to Aussie FY if not 01/04/ to 31/03...

5. ## Re: Formula to Sum by Month and Financial Year

you can also use a pivot table to get the result.

6. ## Re: Formula to Sum by Month and Financial Year

Hi Siva, thank you for the great example. Just one question, our financial year starts from 1 July and ends on the 30 June. I tried to work out your formula by making the following changes:

SUMPRODUCT((\$A\$10:\$A\$20>=DATE(2000+LEFT(\$A3,2)*1,4,1))*(\$A\$10:\$A\$20<=DATE(2000+LEFT(\$A3,2)*1+1,3,31))*(B\$10:B\$20))
to
SUMPRODUCT((\$A\$10:\$A\$20>=DATE(2000+LEFT(\$A3,2)*1,7,1))*(\$A\$10:\$A\$20<=DATE(2000+LEFT(\$A3,2)*1+1,6,30))*(B\$10:B\$20))

but I seem to be having a problem...
kind regards Colin

7. ## Re: Formula to Sum by Month and Financial Year

Yes it is working, see the attached file

8. ## Re: Formula to Sum by Month and Financial Year

And my version set to your FY...

Though, to be honest, I prefer nflsales's method to set the dates!!

9. ## Re: Formula to Sum by Month and Financial Year

Thank you Siva, you have given me the answer.

10. ## Re: Formula to Sum by Month and Financial Year

Hi Glenn, I do like the way you did the formula, as it enable me to see the date. I tried to modify the formula to calculate the total for each month by financial year, but my attempts did not work, for example Nov 2012, did not give me the correct answer:

=SUMPRODUCT(--(\$A\$9:\$A\$99>="01/11/2012"+0)*(\$A\$9:\$A\$99<="30/11/2012"+0),B\$9:E\$99)

Thanks for your input.

11. ## Re: Formula to Sum by Month and Financial Year

I never even noticed that bit. Try this. There is a bit of manual intervention needed in Februarys of Leap Years, where you have to change the 28 in column P to a 29. I also rearranged the months to be in sequence for the Aussie FY.

12. ## Re: Formula to Sum by Month and Financial Year

Originally Posted by Glenn Kennedy
Try this. There is a bit of manual intervention needed in Februarys of Leap Years, where you have to change the 28 in column P to a 29. I also rearranged the months to be in sequence for the Aussie FY.
Great, thanks heaps, it works perfectly...

##### Users Browsing this Thread

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

#### 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