Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

index match formula to sum date for a month

Posted on 2014-01-27
3
Medium Priority
?
1,148 Views
Last Modified: 2014-01-28
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
Comment
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
  • Learn & ask questions
  • 2
3 Comments
 
LVL 33

Expert Comment

by:Rob Henson
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

by:Steve
Steve earned 1000 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

by:
Rob Henson earned 1000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

718 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question