Find date in another sheet and sum 12 cells

**burgiz** · 02-04-2021, 04:13 AM

Hi guys,

I love this place, I always get good and useful feedback on issues and ideas.

Now, I have a new issue that I can't find the correct solution for.

In my overview I have this formula:

=VLOOKUP($C$4;INDIRECT("'"&A5&"'!B24:D119");3;FALSE)

That works like a charm and shows me the correct number.

Now, I would also like to sum up 12 cells (interests for the 12 months (including the current month) to be summed in my overview).

Is it possible?

My setup is like this: "Overview"-sheet contains name of alle sheets and a formula to extract the current debt based on a date i submit to the sheet - e.g. 31.12.2020.
Is it possible to tell Excel to find this date, and "autosum" this date and the last 11 cells so I have 12 months interest?

Please see attached file which is the actual file per agreement. The yellow fields I would like to sum when selecting the date 01.07.2017 in my overview.

* Updated with new Excel file *

**AliGW** · 02-04-2021, 04:18 AM

Where is the overview sheet containing your formula? I see only one sheet here.

**Pete_UK** · 02-04-2021, 04:26 AM

I can't see an Overview sheet in your sample file, but basically you can use a SUM function which contains INDEX/MATCH functions which define the range you want to sum.

Hope this helps.

Pete

**burgiz** · 02-04-2021, 05:24 AM

Sorry for the inconvenience, I have just added the correct file.

**Pete_UK** · 02-04-2021, 05:27 AM

Isn't that the same file?

Pete

**burgiz** · 02-04-2021, 05:33 AM

Originally Posted by Pete_UK

Isn't that the same file?

Pete

You are right, but now the files should be correct - I'm sorry :-)

**Pete_UK** · 02-04-2021, 05:54 AM

You can use this formula in E3:

=SUM(INDEX(INDIRECT("'"&A3&"'!F:F");MATCH($D$1;INDIRECT("'"&A3&"'!B:B");0)):INDEX(INDIRECT("'"&A3&"'!F:F");MATCH($D$1;INDIRECT("'"&A3&"'!B:B");0)-11))

to sum 12 cells from column F in the sheet whose name is in A3.

Hope this helps.

Pete

**Glenn Kennedy** · 02-04-2021, 06:02 AM

On an aside, I wouldn't use whole column references within an INDIRECT. It can get very slow on big sheets with lots of formulae.

**Pete_UK** · 02-04-2021, 06:10 AM

Hi Glenn,

I agree, but the OP used this range in the first post B24:D119, but that wouldn't have worked with the sample file which only had data up to row 19, so I figured if I put in full column ranges then the OP could adjust those quite easily to suit the real data. I suppose I should have pointed that out, so thanks.

Pete

**burgiz** · 02-04-2021, 09:13 AM

Hi guys, and especially you Pete_UK, thank you very much for your answer! It did the job just perfect - thank you :-)

Find date in another sheet and sum 12 cells

LinkBack

Thread Tools

Rate This Thread

Display

Find date in another sheet and sum 12 cells

Re: Find date in another sheet and sum 12 cells

Re: Find date in another sheet and sum 12 cells

Re: Find date in another sheet and sum 12 cells

Re: Find date in another sheet and sum 12 cells

Re: Find date in another sheet and sum 12 cells

Re: Find date in another sheet and sum 12 cells

Re: Find date in another sheet and sum 12 cells

Re: Find date in another sheet and sum 12 cells

Re: Find date in another sheet and sum 12 cells

Thread Information

Users Browsing this Thread

Similar Threads

Copy cell from one sheet, find coresponding date on 2nd sheet data table, paste value

[SOLVED] find date that matches date on my sheet and copy from table below.

Find the cell that contains a date on sheet 1 using Excel 2003 then move to the next sheet

Macro to find Date Cells > Current Date and delete everything to left of that cell

[SOLVED] Cells.Find xlDate does not find first date in sheet unless date format is dd/mm/yyyy

Find a date value in a sheet and delete all cells to the right of it in the row

[SOLVED] Find the date and copy the values from the colum of the date find and paste to other sheet

Bookmarks

Bookmarks

Posting Permissions