Sumifs with date criteria [SOLVED]

[chloec]

I always find it confusing to create formulas with dates. Especially when the formulas have different date formats. Any help or places for me to learn more how to manage dates is very much appreciated.


Here's my specific problem:


Formula:

SUMIFS('[GOS 8-12-13.xlsx]GOS - Detail(1)'!$AB:$AB,'[GOS 8-12-13.xlsx]GOS - Detail(1)'!$E:$E,$D$3,TEXT('[GOS 8-12-13.xlsx]GOS - Detail(1)'!$Y:$Y,"yyyy"),Year($D$1,"yyyy"))


The last part of the formula is giving trouble.


GOS Detail $Y:$Y is structured like this:

02/22/2013

02/22/2013

02/22/2013

03/01/2013

03/08/2013


D1 is structured like this:

01/01/2013


I want the last part of the formula to consider summing the sum data if year is 2013.


Thanks for the assistance.

 

[Hui]

Chloec


Dates are simply Numbers, so don't get hung up on the Format of the Date, that is simply how they are presented to us, but the Actual date behind is still a number.


Dates are the Integer part of the number

Times are the Decimal part of the number


In your case try:

=SUMIFS('[GOS 8-12-13.xlsx]GOS - Detail(1)'!$AB:$AB,'[GOS 8-12-13.xlsx]GOS - Detail(1)'!$E:$E,$D$3,TEXT('[GOS 8-12-13.xlsx]GOS - Year(Detail(1)'!$Y:$Y), 2013))

 

[Sara]

Hi there


Date calcs are tricky to get your head around.

This formula should work for you.

=SUMIFS('[GOS 8-12-13.xlsx]GOS - Detail(1)'!$AB:$AB,'[GOS 8-12-13.xlsx]GOS - Detail(1)'!$E:$E,$D$3,'[GOS 8-12-13.xlsx]GOS - Detail(1)'!$Y:$Y,">="&$D$1,'[GOS 8-12-13.xlsx]GOS - Detail(1)'!$Y:$Y,"<="&EOMONTH($D$1,11))


I don't believe (ninja's correct me if I'm wrong) that the criteria range portion can be a function.


To get the formula to work it, the date range criteria needs to be repeated as greater than this, and less than this.


The criteria for the greater than is ">="&

The criteria for the less than is "<="&

I've adjusted the last criteria so that it is the end of month formula in 11 months time or 31st Dec


This should help

http://chandoo.org/wp/2011/09/27/sum-between-2-dates/

 

[Luke M]

@Sara

Yes and no. The CriteriaRange has to be a range as opposed to a array generated by a function, but it is possible for that range to be generated by a function such as OFFSET, INDEX, or INDIRECT.

 

[chloec]

Thank you all for great feedback. I used this formula:

=SUMIFS('[GOS.xlsx]GOS - Detail(1)'!$AB:$AB,'[GOS.xlsx]Global Open Supply - Detail(1)'!$E:$E,$D$3,'[GOS.xlsx]GOS - Detail(1)'!$Y:$Y,">="&$AD$52)


where AD52 = is 1/1/13. This worked great. Thanks again!