SUMIF that returns values in a date range

[Tony C]

A1 and B1 are start and end dates that can be selected to make a date range

So for example:

A1 = 1/9/14
A2 = 1/11/14

Let's say column C is a list of dates and column D is a list of numerical values I want to add only for that date range.

I know I can do a some if to return the sum of values that are greater than... or to return the sume of values that are less than...

...but is it possible to do a SUMIF that will return the values that are between A1 and A2?

So...

I would want it to return the sum of the dates between A1 (in this case 1/9/14) and B1 (in this case 1/11/14) so it would return a value of 25 (5+18+2).


Thanks in advance for your help. I'm really new at this and have just been learning by playing around with Excel.

 

[AIM]

Hi Tony,

You can use this formula to get results between the 2 dates

=SUMIFS($D$1:$D$5,$C$1:$C$5,">="&$A$1,$C$1:$C$5,"<="&$A$2)

Regards!!

 

[NARAYANK991]

Hi Tony ,

To add to what Animesh has posted , which certainly will work , is that the SUMIFS function is available only in Excel 2007 and later versions.

To replicate the same functionality in Excel 2003 , we can use the SUMPRODUCT function , in which case the formula would be :

=SUMPRODUCT(($C$1:$C$5>=$A$1)*($C$1:$C$5<=$A$2)*($D$1:$D$5))

There is a more complicated alternative using SUMIF , but using SUMPRODUCT is preferable.

Narayan

 

[Tony C]

Hi Tony,

You can use this formula to get results between the 2 dates

=SUMIFS($D$1:$D$5,$C$1:$C$5,">="&$A$1,$C$1:$C$5,"<="&$A$2)

Regards!!

Hmmm... I tried it in the example and it works fine, but when I try it on my actual worksheet using the data I need, it's giving me an error...

=SUMIFS('Pivotlink Data'!$F:$F,'Pivotlink Data'!$B:$B,$B8,'Pivotlink Data'!$C:$C,"POSTPAID",">="&$C$2,'Pivotlink Data'!$E:$E,"<="&$C$3)

It says "You've entered too few arguements for this function and when I click 'ok' it highlights the end of it... $C$3

I've combed through it a few times and am not seeing any errors... might you be able to help me understand what I'm messing up?

Thanks again for all your help.

I should clarify... I am using 2007

 

[Tony C]

Hi Tony ,

To add to what Animesh has posted , which certainly will work , is that the SUMIFS function is available only in Excel 2007 and later versions.

To replicate the same functionality in Excel 2003 , we can use the SUMPRODUCT function , in which case the formula would be :

=SUMPRODUCT(($C$1:$C$5>=$A$1)*($C$1:$C$5<=$A$2)*($D$1:$D$5))

There is a more complicated alternative using SUMIF , but using SUMPRODUCT is preferable.

Narayan

Thank you. Fortunately that won't be necessary as I am using 2007

 

[Somendra Misra]

@Tony C

In your formula ">="&$C$2 is passed as an argument for the criteria range, but this this criteria. Insert a range for this criteria. You will get it.

Regards,

 

[Tony C]

@Tony C

In your formula ">="&$C$2 is passed as an argument for the criteria range, but this this criteria. Insert a range for this criteria. You will get it.

Regards,

I'm a little confused by that. In the formula...

Column E is a list of dates. Cells C2 and C3 are two different dates. I'm wanting it
I'm filtering columns B and C for certain criteria, and when that criteria is met, I'm wanting it to filter a date range from Column E for any dates that are between or including the dates that are in C2 and C3 and return the SUM of the values in column F for those dates :/

I was under the impression that ">="&$C$2,'Pivotlink Data'!$E:$E,"<="&$C$3 is saying to only consider rows in EE that have a value between C2 and C3... I thought that was all one argument/criteria range?

 

[NARAYANK991]

Hi Tony ,

In SUMIFS , the syntax is : =SUMIFS(sum_range , criteria_range1 , criteria1 , criteria_range2 , criteria2 , ...) , where the first two parameters are ranges.

If we take the formula you have posted :

=SUMIFS('Pivotlink Data'!$F:$F,'Pivotlink Data'!$B:$B,$B8,'Pivotlink Data'!$C:$C,"POSTPAID",">="&$C$2,'Pivotlink Data'!$E:$E,"<="&$C$3)

the sum_range is : 'Pivotlink Data'!$F:$F

the criteria_range1 is : 'Pivotlink Data'!$B:$B

the criteria1 is : $B8

the criteria_range2 is : 'Pivotlink Data'!$C:$C

the criteria2 is : "POSTPAID"

and then there is a mismatch , since what follows is a criteria instead of a range.

Narayan

 

[NARAYANK991]

Hi Tony ,

You will have to specify it as follows :

'Pivotlink Data'!$E:$E, ">="&$C$2, 'Pivotlink Data'!$E:$E, "<="&$C$3

You need to repeat the range so that the order of the parameters is strictly followed.

Narayan

 

[Tony C]

Hi Tony ,

In SUMIFS , the syntax is : =SUMIFS(sum_range , criteria_range1 , criteria1 , criteria_range2 , criteria2 , ...) , where the first two parameters are ranges.

If we take the formula you have posted :

=SUMIFS('Pivotlink Data'!$F:$F,'Pivotlink Data'!$B:$B,$B8,'Pivotlink Data'!$C:$C,"POSTPAID",">="&$C$2,'Pivotlink Data'!$E:$E,"<="&$C$3)

the sum_range is : 'Pivotlink Data'!$F:$F

the criteria_range1 is : 'Pivotlink Data'!$B:$B

the criteria1 is : $B8

the criteria_range2 is : 'Pivotlink Data'!$C:$C

the criteria2 is : "POSTPAID"

and then there is a mismatch , since what follows is a criteria instead of a range.

Narayan

OHHHHHHHHH I got it...

I needed to add 'Pivotlink Data'!$E:$E, in front of "">="&$C$2

=SUMIFS('Pivotlink Data'!$F:$F,'Pivotlink Data'!$B:$B,$B8,'Pivotlink Data'!$C:$C,"POSTPAID",'Pivotlink Data'!$E:$E,">="&$C$2,'Pivotlink Data'!$E:$E,"<="&$C$3)



It's working now.

Thank you so much!

You all are so helpful!!!