Year to date to week - Index Match & Sum Ifs function?

**JonasH.123** · 02-11-2018, 06:37 AM

Hello Everyone!

I am new to the Excel-community and just recently I struggled with setting up a formula, that shows the sum of the year to date numbers of the past and current month.
In the current month it should be possible to differentiate until which week the sum should be drawn (Week 1, 2,3 and 4).
I tried with a combination of a Index Match function and Sum if function, but I could not find the right way..

To make the situation a bit clearer:

'Database':

Country Week Jan Feb Mar
China Week 1 1000 2000 400
China Week 2 400 750 700
China Week 3 1200 3000 2100
China Week 4 800 1200 300

'Output table':

Month: February

Country Week 1 Week 2 Week 3
China 300 X

Assuming week 2 in February has just passed by, the goal is to show the sum of January and Week 1 & 2 of February below 'Week 2'.

Does anyone have an idea? I'd be very grateful for any advice.

**AliGW** · 02-11-2018, 08:00 AM

Will you please attach a sample Excel workbook?

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

**JonasH.123** · 02-11-2018, 09:16 AM

Hello Ali,

thank you very much for the advice and the quick response!

Please find attached the example of what I am trying to achieve.

Assuming that the second week of February has just passed, the 'output table' in week 2 shows the sum of the January weeks plus week 1 & week 2 of February.

**JonasH.123** · 02-12-2018, 11:24 PM

Anyone an idea?

**JeteMc** · 02-17-2018, 05:29 PM

Hello JonasH. 123 and Welcome to Excel Forum.
This proposed solution utilizes a helper table which may be moved and/or hidden for aesthetic purposes (in the attached file it is placed on Sheet 1).
The formula that populates the helper table is:

Formula:

Please Login or Register  to view this content.

The array entered formula* that populates the Year & Week to Date table is:

Formula:

Please Login or Register  to view this content.

*Array entered formulas are confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
The Year & Week to Date table has conditional formatting applied to hide zero values.
Let us know if you have any questions.

**JonasH.123** · 02-22-2018, 05:22 AM

Dear Jete MC,

I want to thank you sincerely for taking your time on this - the formula works perfectly :-)

Best regards!
Jonas

**JeteMc** · 02-22-2018, 11:04 AM

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.

Year to date to week - Index Match & Sum Ifs function?

LinkBack

Thread Tools

Rate This Thread

Display

Year to date to week - Index Match & Sum Ifs function?

Re: Year to date to week - Index Match & Sum Ifs function?

Re: Year to date to week - Index Match & Sum Ifs function?

Re: Year to date to week - Index Match & Sum Ifs function?

Re: Year to date to week - Index Match & Sum Ifs function?

Re: Year to date to week - Index Match & Sum Ifs function?

Re: Year to date to week - Index Match & Sum Ifs function?

Thread Information

Users Browsing this Thread

Similar Threads

IF function with YEAR and INDEX MATCH? How to?

Calculation of Week Number & Week Ending Date formula from a Year Cell

[SOLVED] Index Match with multiple criteria - Return top sellers for certain week from year data

[SOLVED] Match function in VBA ignoring year of date

[SOLVED] Get the date of saturday in a week from a week number / year

[SOLVED] MATCH-INDEX function for retrieving specific year data in a table...

[SOLVED] Calculating a Date Based on Year, Week Number and Day of the Week

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions