• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1225
  • Last Modified:

index match formula to sum date for a month

Hi Expert's excel 2007

is it possible to the all the values for all the dates in a given month using a index match formula..the dates are on the horizontal match...
             01/11/2013.   02/11/2013.   03/11/2013
abd.          12.                       9.                         7
cgd.           11.                      8.                         3

So in my new table I want to show a total for Nov 13 of abd etc..
  • 2
2 Solutions
Rob HensonFinance AnalystCommented:
Index & macth will return the value from one cell rather than a range.

Maybe you can use the SUMIFS function.

Do you require the match function to determine which row the SUM is coming from, ie identify which row is "abd" and sumup that row if the header for the column is within the specified month.

Rob H
Using an array formula like:
Will do the trick...

See attachment for a working example
(press [ctrl]+[shift]+[enter] when entering an array formula)
Rob HensonFinance AnalystCommented:
See attached with a few options:

Data tab - sample data as per question with a few extra columns to demo extra dates and suggestioin with SUMIFS function using single date cell as reference
SUMIFS - summary table using SUMIFS with multiple date cells for reference
OFFSET - quite invlolved formula to determine range of data based on id input and date, assumes that data is in chronological order in columns; finds first column within date range and uses columns to the right of first column found equal to number of columns that match date range.

Rob H
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now