Column totals, with exceptions and equal to current/next month

[JMWNH]

Hi

I have tried SUMIFS and SUMPRODUCTS but still cannot get this to work, and have tried MS community. 

I have a report that contains annual finance data and i am trying to produce a dashboard. I have developed most of this, with the exception of it being able to extract for forecast for the current and next month. 

I have added a table below, and fingers crossed this makes sense. 

I want to be able to write formula that will extract the current months data, this month is in a cell. So it is currently April, i want to extract Apr and May into different cells on the dashboard. When it is May, i want to extract May and Jun into set cells in the dashboard. 

The dashboard is broken down into Option A, B, C etc and the task codes also have 6 in total that i do not want to calculate so to only count the colum for the month, and sum the options, but exclude set tasks.

Name	Task	Apr	May	Jun
Option A	2.1	£1.12	£1.13	£1.14
Option A	2.2	£1.24	£1.25	£1.26
Option A	9.1	£1	£1	£1
Option B	2.1	£1.32	£1.33	£1.34
Option B	2.2	£1.40	£1.41	£1.42
Option B	9.1	£1	£1	£1
Option C	2.1	£1.41	£1.42	£1.43
Option C	2.2	£1.42	£1.43	£1.44
Option C	9.1	£1	£1	£1

I hope this makes sense, thank you all

[#2]

@JMWNH 

Can you provide an example of the desired result?

[#3]

Hi Yes sorry i meant to do that. 

Image attached. 

The top table is current month

The bottom is next month

Column C is the forecast for each, and

Column B is the options (column A is hidden for sensitivity but is not used for anything other than completeness in these tables. 

The titles of In month and next month are and =TODAY() displayed a month rather than date. 

Cells B1, 2, 14, 15 - are me typing those to try and use them as a reference for the formula (when 1:1 from the report matches that month count that column for each option, and exclude certain tasks)

When the month then moves onto the next then id like it to automatically move on. as the report contains this financial years data. thanks 

[#4]

@JMWNH 

Thanks. You mentioned "the task codes also have 6 in total that i do not want to calculate". Can you elaborate?

[#5]

Hi - yes thank you.
The codes are assigned to activites carried out, there are about 25 or so in total. but 6 of those (9.1) being one of them arent activities that need to be calculated and must be excluded from the sum to give an accurate figure for forecast (they also have to be excluded for monthly spend too, but i have written that formula already, but that foruma doesnt need to move one month to the next)

[#6 · Accepted solution]

@JMWNH 

See if the attached workbook gets you started. Note that the months are all real dates.

The second sheet cannot use tables, for tables do not allow dynamic array formulas.

[#7]

Hi Hans

@Hans Vogelaar  - thank you very much for this, it worked. It was way above what i could conjour up myself. 

I really appreciate the help. 

Have a great weekend