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

# 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...
ie.
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..
0
route217
• 2
2 Solutions

Finance 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.

Thanks
Rob H
0

Commented:
Using an array formula like:
=SUM(IF(A4:A5=B9,IF(MONTH(B3:L3)=B8,B4:L5)))
Will do the trick...

See attachment for a working example
(press [ctrl]+[shift]+[enter] when entering an array formula)
example.xlsx
0

Finance 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.

Thanks
Rob H
SUMIFS-or-OFFSET.xlsx
0
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.

## Featured Post

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