Forum Discussion
Solved
Column totals, with exceptions and equal to current/next month
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
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.
Can you provide an example of the desired result?
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
Thanks. You mentioned "the task codes also have 6 in total that i do not want to calculate". Can you elaborate?
