LinkBack URL
About LinkBacks
Hi all ,
Could some one kindly advice how do I write a excel formula to calculate amounts that falls under particular months.
Please find attached excel file with example of what I am looking for .
Regards,
Hudson
Hi all ,
Could some one kindly advice how do I write a excel formula to calculate amounts that falls under particular months.
Please find attached excel file with example of what I am looking for .
Regards,
Hudson
Not 100% sure that I've grasped what you need here, but the below formula will give you the expected result shown in your example.
Formula:
Please Login or Register to view this content.
BSB
Hudson,
you could create a new data column with the formula: "=month(A2)".
And then you could run a SumIf based on this column.
I've made these changes to your excel in green. Please see attached.
Let me know if it works.
Cheers
<-- If you're happy & you know it...click the star.:-)
Hi amith - thankyou so much for your reply but I unable to get the information what I am supposed to .. because I have date from last years may too..
However I have attached another sheet too for your reference.
Use the SUMPRODUCT function.
thank you so much for your email . formula you gave could not help me in getting what I am looking for .
maybe I was not clear in stating my problem , hear is the requirement I wanted to sum between two dates , firstly from date could be anything , to date would be my complete current month ( i.e june and if I am in july my current month would be July)..
I have tried a formula using SUMIFS ..=SUMIFS(B:B,A:A,">="&A2,C:C,"<="&MONTH(TODAY()))
but for this I need to creat helper column ..
Try
=SUMPRODUCT(($B$2:$B$227>=$F$3)*($B$2:$B$227<=EOMONTH(TODAY(),0))*($C$2:$C$227))
OR
=SUMIFS($C:$C,$B:$B,">=" & $F$3,B:$B,"<=" &EOMONTH(TODAY(),0))
The latter is the formula supplied in post #2 by BSB though EOMONTH was following month i.e. July: did you try it? ???
[=SUMIFS(B:B,A:A,">="&F3,A:A,"<="&EOMONTH(TODAY(),1))]
No helper column required.
Last edited by JohnTopley; 06-01-2016 at 01:03 PM.
John- this is fantastic !!!! .. I got what I am looking for . thank you all once again .
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks