Formula: formula that bases total on current month throughout the year

[Churchy LaFemme]

Not sure how to phrase this - can I create a single-cell formula that knows what month it is and adjusts accordingly?

Over a two-year period, the project team needs to hold a total of 52 meetings (with guidance to have 2 meetings each month with an additional 4 whenever). My spreadsheet lists all the meetings by date.

Tallying up meetings held and meetings needed for each year is easy.

On the dashboard, I also want to have a sumif or countif that shows meetings needed in the current month.

A formula that recognizes what month we are in – is that even possible?

So, for 2018, there is one cell that shows needed meetings as of the current month.
In January, they need 6 (the two monthly meetings and the additional 4 meetings needed for the CY).
If they only have one meeting in January, then in February, they need 7. (The missing January meeting, both February meetings, and the 4 additional meetings needed over the course of the year.)
In April, if they had 2 meetings each month and also knocked out 3 of the additional meetings, they need 3 meetings that month.
In October, assuming they knocked out the 4 additional meetings and each of the 2 monthly meeting for Jan-Sept, then they need 2.

That is, in the third month, the total is 10, minus the number of meetings held. In the fourth month, the total is 12, minus the number of meetings held.

Is this possible? Can I tie month to (Today) in some way?

Or should I just give up and create a hidden sheet that calcs per month and then base the dashboard formula off of that? (Or try to do something in VBA which is often not allowed by our install.)

This example has the 52 meetings divided evenly between the two years.

	2018	2019
Annual Compliance Check-up Meetings Held	0	0
Annual Compliance Check-up Meetings to Schedule	26	26
Annual Compliance Check-up Meetings to Schedule this Month	6	0

<tbody>
</tbody>

And thoughts on this bugger appreciated.

 

[oldbrewer]

the today() function returns the current date - format that as text mmm

so in A1 =text(today(),"mmm")

so it says jan

but I cannot understand what you want, yet...

 

[oldbrewer]

				jan	feb	mar	apr	may	june		row 2
			planned	4	2	2	2	4	2
			held	2	1	3	2	4	1
			short	2	3	2	2	2	3
			col D						col J
				let us consider mar
				clearly jan to mar 8 planned 6 held so we are 2 short
				formula for 2 short in jan is
				=SUM($E$3:E3)-SUM($E$4:E4)
				dragged across
				say current month is june and you want "short status" for mar
				mar
				2
				formula giving this 2 is
				=OFFSET($D$2,3,MATCH(E22,$E$2:$J$2,0))

<colgroup><col width="64" span="14" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[Churchy LaFemme]

I want a single cell that shows a total of events needed. In January, the calculation is X-[number of events logged]. In February, the calculation changes to (X+2)-[number of events logged.] In March, (x+4)-[number of events logged].

My guess is this is not possible. That I will have to created a hidden sheet that manages the month-by-month data and then use a formula that calculates a total based on the hidden-sheet data.

But then I wondered if any of the brilliant minds of Mr. Excel could sort this out!

 

[jtakw]

Hi,

Saw your post earlier today, but didn't have time to work on it then, I believe this does what you're describing:

B4 is your formula, Columns E and G are there just to show you the counts for different months (you DON'T need to add these to your worksheet).

Book1
	A	B	C	D	E	F	G
1		2018	2019		1/1/2018		5
2	Annual Compliance Check-up Meetings Held	1	0		2/1/2018		7
3	Annual Compliance Check-up Meetings to Schedule	26	26		3/1/2018		9
4	Annual Compliance Check-up Meetings to Schedule this Month	5	0		4/1/2018		11
5					5/1/2018		13
6					6/1/2018		15
7					7/1/2018		17
8					8/1/2018		19
9					9/1/2018		21
10					10/1/2018		23
11					11/1/2018		25
12					12/1/2018		27
Sheet18

Cell Formulas
Range	Formula
B4	=MONTH(TODAY())*2+4-B2
G1	=MONTH(E1)*2+4-B$2

 

[Churchy LaFemme]

Wow. This is so much easier than I expected.

Thank you for the assist!

 

[jtakw]

Wow. This is so much easier than I expected.

Thank you for the assist!

You're welcome, but we're not quite done yet, now that I know this is what you want, we need to improve the formula to deal with what to do when 2019 comes around:

Book1
	A	B	C	D	E	G	H	I	K
1		2018	2019		1/1/2018	5		1/1/2019	29
2	Annual Compliance Check-up Meetings Held	1	0		2/1/2018	7		2/1/2019	31
3	Annual Compliance Check-up Meetings to Schedule	26	26		3/1/2018	9		3/1/2019	33
4	Annual Compliance Check-up Meetings to Schedule this Month	5	0		4/1/2018	11		4/1/2019	35
5					5/1/2018	13		5/1/2019	37
6					6/1/2018	15		6/1/2019	39
7					7/1/2018	17		7/1/2019	41
8					8/1/2018	19		8/1/2019	43
9					9/1/2018	21		9/1/2019	45
10					10/1/2018	23		10/1/2019	47
11					11/1/2018	25		11/1/2019	49
12					12/1/2018	27		12/1/2019	51
Sheet18

Cell Formulas
Range	Formula
B4	=IF(YEAR(TODAY())>B1,B3-B2,IF(YEAR(TODAY())=B1,MONTH(TODAY())*2+4-B2,0))
C4	=IF(YEAR(TODAY())>C1,C3-C2,IF(YEAR(TODAY())=C1,MONTH(TODAY())*2+B4-C2,0))
G1	=MONTH(E1)*2+4-B$2
K1	=MONTH(I1)*2+G$12-C$2

B4 and C4 formulae are slightly different.
When we get to 1/1/2019, B4 will No longer look at the current Month (which will give you incorrect result), it will show what the total required meetings less meetings already held, and for the 2019 column (C4), it will show 0 until 1/1/2019.
Then, starting 1/1/2019, instead of adding 4 to the 2 monthly meetings, it adds remaining required meetings from December of 2018.

Again, Columns E, G, I, K are there just to demonstrate the counts through out 2018 and 2019, you DON'T need to add these in your worksheet.

 

[Churchy LaFemme]

In case anyone comes to this old thread for reference, I have a solution that is working very well for now. I may have to spend more time with jtakw's solution when 2019 rolls around.

This is based on the answers in the thread, and the poked a bit.

=A4-(SUMIF(CMD[Start Date],">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1),CMD[Total]))

A4 is a cell in the document header that shows the number of meetings required per month.
CMD is a table showing meeting details.
Start Date is the start date of each meet-up, formatted as a date.
Total is formatted as a number. This column contains "1" if the full agenda was covered, or .5 or .75 if there is additional meeting time required to tick off all the compliance items.

This formula has shown the correct total needed each month, and correctly reset showing 6 as the monthly needed total.

Thanks again, Mr. Excel.

 

[jtakw]

Hi, glad you have something that's working for you.
This is just FYI, the DATE part of your formula DATE(YEAR(TODAY()),MONTH(TODAY()),1) can also be done like this: EOMONTH(TODAY(),-1)+1