Calculation using a variable date range for lookup

[robf]

Hello!


I am trying to calculate a total for a given category for a rolling 12 month period, going back 12 months from today’s date.


Data format example:

A5:A200 Category (e.g. Eggs, Milk or Cheese)


B5:B200 Date of activity in category (transactions). Dates in list are not consecutive dates – there may be missing days. Date range starts arbitrarily (e.g. 2009/10/25) and extends into the future (2012/02/27)


C5:C200 Numeric value of activity in the category on that date


Date values are not unique. For example a snapshot of the data might look like this:

Mar 10, 2011 Eggs 20

Mar 10, 2011 Milk 25

April 7, 2011 Cheese 10

April 9, 2011 Cheese 17

April 9, 2011 Eggs 2

April 9, 2011 Eggs 6

Feb 7, 2012 Milk 15

Feb 7, 2012 Cheese 5


Problem: Find total Eggs for previous 12 months from today


Thanks in advance for your help!

 

[Hui]

Robf


Firstly, Welcome to the Chandoo.org Forums


Your Columns in the descriptions of your data don't match the sample data

But using the sample data:


You have two options


=SUMPRODUCT((A5:A200>TODAY()-366)*(A5:A200<=TODAY())*(UPPER(B5:B200)="EGGS"),(C5:C200))

or

=SUMIFS(C5:C200,A5:A200,">"&TODAY()-365,A5:A200,"<="&TODAY(),B5:B200,"Eggs")

 

[robf]

Thank you very much! I have been stuck on the variable date range criteria for a while.