Difficulty with SumIFs for specific dates in every Year

[BeepBeep]

Hi all,

Good Afternoon,

I have my first column with pure Dates and second column with Sales running for 10 years, starting from 200 5 to 2014.

Requirement-I:

I want to calculate Sum of sales ONLY on some specific days say, Jan-1st, Dec-25th for all years.

If I choose 25th Dec, I want the Sum of Sales for 25th Dec for all the 10 years from 2005 to 2014.


Requirement-II:

Same way, I want to calculate Sum of sales between two specific dates for all the 10 years (2005 to 2014) say, Jan 8th to Jan 15th.

If I choose Jan 8th to Jan 15th, I want the Sum of Sales between those two dates from all the years i.e. 2005 to 2014.

I know standard SUMIFS function, but I do not know how to use it in this situation. Any help from anybody will be greatly appreciated.

Thank you.
BepBeep

 

[Mike LH]

Hi,

This would sum 1 Jan for all years

=SUMPRODUCT((TEXT(A2:A1000,"DDMM")="0101")*(B2:B1000))

and like wise for 25 Dec


=SUMPRODUCT((TEXT(A2:A1000,"DDMM")="2512")*(B2:B1000))

and between 2 dates in this case 8 Jan and 15 jan inclusive.


=SUMPRODUCT((TEXT(A2:A1000,"DDMM")>="0801")*(TEXT(A2:A1000,"DDMM")<="1501")*(B2:B1000))

 

[gaz_chops]

Try this change the ranges to suit,
C=Range to sum
F7 & F8 = Start & End dates (put same date in both to sum for 1 date)

=SUMIFS(C2:C14,A2:A14,">="&F7,A2:A14,"<="&F8)

Edit: realised you want it for ALL years, so this won't work.

 

[BeepBeep]

Its working super fine, ..Thank you so much, both of you for your effort, Mike and Gaz.