Finding maximum date in a date range and do the sumifs

[Niraj Baraili]

Hi Experts,

Need your help on this.

I have attached sample file for your reference where i clearly explained the requirement.

 

[NARAYANK991]

Hi ,

Your thread title does not match your explanation , since June 20 is not the maximum date ; if we go by your explanation , then the following formula will work :

=SUMIFS($M$2:$M$26,$L$2:$L$26,">=" & "01-06-2016",$L$2:$L$26,"<=" & "20-06-2016")

If we go by the thread title , then the following formula will work :

=SUMIFS($M$2:$M$26,$L$2:$L$26,">=" & "01-06-2016",$L$2:$L$26,"<=" & MAX($L$2:$L$26))

Narayan

 

[Niraj Baraili]

Thank you for your reply.

I think i have not explained it clearly.
Based on the weekdate in col b for 2016, i need to calculate MTD value for 2015.

In cell D8 (June MTD), i need a formula to sum the previous year (in this case 2015) Item1 based on the Week date in col B.
Also, range in D8 should include 1 to 20 for 2015. If Weekdate in 2016 is 6/20/2016, D8 maximum date is 6/26/2015, but Weekdate is 6/27/2016 then maximum date should include 6/30/2015 only not 7/3/2015.

 

[NARAYANK991]

Hi ,

It is still not clear.

Please specify what are the inputs which will be used to derive the result.

You have mentioned Week Date , but Week Date is an entire column ; which specific cell is to be used to decide whether Week Date is 6/20/2016 or 6/27/2016 ?

Narayan

 

[Niraj Baraili]

Weekdate is the Starting day of a week for which i am using formula "=IF(WEEKDAY(L2,1)=1,L2,L2-(WEEKDAY(L2,1)-1))+1". To sum the Item1 date for 2015 in col D, i have converted Week Date col B to Week Num in Col A and based on this Week Num i have summed Item1 for 2015.

I can put max date=6/20/2016+6 and min date=6/1/2016 and using the formula which you provided in previous reply and find the MTD data for 2015. But for the week date 6/27/2016,it is going till 7/3/2016. That i don't want and it should be till 30th June.

 

[NARAYANK991]

Hi ,

See if this is acceptable.

Narayan

 

[Niraj Baraili]

Excellent. It's serve my purpose.

Thank you very much Sir.