• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sumifs Formula to extract data based on 2 fiscal periods.

K

kensbro

New Member
I am attempting to retrieve data based on "service dates" prior to July 2010 and dates in July 2010 or later. Results for each of these periods will be placed in cells A1 &B1. However, because the fiscal year runs from July 10-June 11, i can foresee a problem when January 11 comes around as data from that month will be included as prior to July 10 due to the current structure of my formulas below:


=SUMIFS($E$2:$E$54,$D$2:$D$54,K15,$C$2:$C$54,K9,$A$2:$A$54,"<"&MONTH(J2))

=SUMIFS($E$2:$E$54,$D$2:$D$54,K15,$C$2:$C$54,K9,$A$2:$A$54,">="&MONTH(J2))


Any suggestions on how to modify the above formulas to eliminate this issue?
 
If you have dates in Column A use this

=SUMIFS($E$2:$E$54,$D$2:$D$54,K15,$C$2:$C$54,K9,$A$2:$A$54,"<"&J2)

=SUMIFS($E$2:$E$54,$D$2:$D$54,K15,$C$2:$C$54,K9,$A$2:$A$54,">="&J2)


If you have month names think about changing them to dates

Ie: Instead of May use 1/5/2011 etc


You said something about cells A1 & A2 but don't use them?
 
Hui...Thanks for the input. I have dates in Column A..But i used the "month" function to convert to month number eg..05/22/2010 = "5". This is the number i used in the formula. I need to extract data for all dates prior to 7/1/2010 and all dates on 7/1/2010 and later and place these in separate cells.


Will your suggested response be applicable to this scenario?
 
You must log in or register to reply here.
Back
Top