sumproduct a date range

Question

Hi

I have 4 columns where I am adding up the data from Column "D" based on the conditions on column A, B, C

In E1 I have an option to mention a date

In column C I have dates.

the condition to  be observered for dates is to consider the days for the month of the day metioned in E1 from 1st of that month till the day mentionedin E1.

Thanks in advance

Answer 1

Try something like,

=SUMPRODUCT((YEAR($C$2:$C$99)=YEAR($E$1))*(MONTH($C$2:$C$99)=MONTH($E$1))*(DAY($C$2:$C$99)<=DAY($E$1))*$D$2:$D$99)

Answer 2

If you plan to put in the date, the first two formulas use DATE(2013,4,1) for 01-Apr-2013. The last formula calculates the date from whatever is input to E1. If you put 01-Feb-2014 in E1, it will use 01-Apr-2013 as the start date and sum everything to 01-Feb-2014. If you used 15-Nov-2012, then it would use 01-Apr-2012 as the start date and sum everything up to 15-Nov-2012. I think the last formula is a better one for you. To test the first day part, put the following into an unused cell,

=DATE(YEAR($E$1)-(MONTH($E$1)<4),4,1)

... and type different date values into E1.

Answer 3

The last time you had a date entered in E1 so i will continue to use that reference. The start of the fiscal year can be hard coded into the formula since you are taking about a FYTD sum. With 01-Jun-2013 in E1 try,

=SUMPRODUCT(($C$2:$C$99>=DATE(2013,4,1))*($C$2:$C$99<$E$1)*$D$2:$D$99)

.. or to use the more efficient SUMIFS() function introduced in Excel 2007,

=SUMIFS($D$2:$D$99, $C$2:$C$99, ">="&DATE(2013,4,1), $C$2:$C$99, "<"&$E$1)

Further calculation should be able to calculate the first day of the fiscal year. Something like,

=SUMIFS($D$2:$D$99, $C$2:$C$99, ">="&DATE(YEAR($E$1)-(MONTH($E$1)<4),4,1), $C$2:$C$99, "<"&$E$1)

You may note that I have consistently used less than E1. Change this to <= if you prefer less than or equal to E1.

Answer 4

Sir,

I have a small query here, is there any method we can use to find the sum of values in a given financial year to a mentioned date

ex: if my financial year s mentioned as 1st April 2013 to 31st march 2014 and I entered a date 1st June 2013,

I want the sum of all values from 1st April 2013 till 1st of June 2013

If I entered 1st February 2014 then the sum should be between 1st April 2013 till 1st February 2014

tried trying to make start and end of financial year with cell reference and got error.

thanks in advance.

Answer 5

Hi,

Try this

=SUMIF($C$2:$C$50,">="&date(year(E1),month(E1),1),$D$2:$D$50)-SUMIF($C$2:$C$50,">"&E1,$D$2:$D$50)

Hope this helps.