Solved

# index match formula to sum date for a month

Posted on 2014-01-27
1,097 Views
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
Question by:route217
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2

LVL 33

Expert Comment

ID: 39811897
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

LVL 24

Assisted Solution

Steve earned 250 total points
ID: 39811946
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

LVL 33

Accepted Solution

Rob Henson earned 250 total points
ID: 39811954
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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
###### Suggested Courses
Course of the Month5 days, 9 hours left to enroll