Recording 12 Month Revenue

[ExcelRose]

Hello.

I'm looking for a way to find a customer's revenue prior to joining a different line of business in my firm. Sheet 1 has the customer's start date and sheet 2 has all of this customer's ID and revenue from 2014 to May 2016. This customer started in January 2015 so I would need to return the sum of the 12 months prior to January 2015 (not including January). Any help would be great!

[tim201110]

date is a number, not text

[JohnTopley]

Try

=SUMPRODUCT((Sheet2!B2:AD10)*(Sheet2!A2:A10=Sheet1!A2)*(Sheet2!B1:AD1>=EOMONTH(Sheet1!B2,-13)+1)*(Sheet2!B1:AD1<=EOMONTH(Sheet1!B2,-1)))

Dates in B1:AD1 must be in Excel date format

[oeldere]

with a helpcel in D2

After that a formula with sum(indirect and adress)

See the green cells in the attached file.

[ExcelRose]

Thanks guys!

[oeldere]

Thanks for the rep.

You're welcome. We appreciate the feedback!

If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.