goldmansnacks
New Member
- Joined
- Jan 6, 2016
- Messages
- 3
I'm having trouble figuring this out. I feel like SUMPRODUCT may be the required formula. Here's the situation:
The application is for income statements.
Across the columns is the month-end dates (1/31, 2/28, 3/31) etc... The column preceding the dates is the line items (revenue, COGS, opex, etc. - but multiple instances of each one (for different entities))
If I pick two dates, say 1/31 and 3/31 in different cells on a new sheet (column A and B), I want to sum the revenue (or whatever line item) for the range of A:C, or 1/31:3/31 on the original data sheet.
Previously, I was using INDEX for the sum range, and MATCH to select the date across the columns, but this doesn't capture a range of columns, it only looks up one date and then SUMIF works.
Ideally, I'd have two inputs: a month end date, and an input below to hard code how many months you want to backtrack from that date (EOMONTH of date, -3 etc)
My description of this may be heavily convoluted, I'll be more than happy to clarify/add color.
Thanks so much in advance - you guys rock.
The application is for income statements.
Across the columns is the month-end dates (1/31, 2/28, 3/31) etc... The column preceding the dates is the line items (revenue, COGS, opex, etc. - but multiple instances of each one (for different entities))
If I pick two dates, say 1/31 and 3/31 in different cells on a new sheet (column A and B), I want to sum the revenue (or whatever line item) for the range of A:C, or 1/31:3/31 on the original data sheet.
Previously, I was using INDEX for the sum range, and MATCH to select the date across the columns, but this doesn't capture a range of columns, it only looks up one date and then SUMIF works.
Ideally, I'd have two inputs: a month end date, and an input below to hard code how many months you want to backtrack from that date (EOMONTH of date, -3 etc)
My description of this may be heavily convoluted, I'll be more than happy to clarify/add color.
Thanks so much in advance - you guys rock.
