YTD Sum of Plan values to be compared to Actual values

[rschegg]

I am currently working on a controlling dashboard for SharePoint, but have to figure out the formulas in Excel first.

I want to compare a YTD planned value (B4) with a YTD actual value (C4) and of that number insert a traffic light showing the YTD status vs. the plan.

Below you see the logic for one year (later on more years will be inserted).

	A	B	C	D	E	F	G	H	I	J	K
1	Today	Q2 2017 Plan
2
3	2017 FY Plan	2017 YTD Plan	2017 YTD Act	Q1 2017 Plan10	Q1 2017 Act	Q2 2017 Plan	Q2 2017 Act	Q3 2017 Plan	Q3 2017 Act	Q4 2017 Plan	Q4 2017 Act
4	50	32	12	10	2	20	10	15	6	5	10

<tbody>
</tbody>

The formulas I used are following:

B2	="Q"&INT((MONTH(TODAY())+2)/3)&" "&YEAR(TODAY())&" "&"Plan"
A4	=SUM(D4:INDEX(D4:K4;MATCH(B1;D3:K3;0)))
B4	=D4+F4+H4+J4

<tbody>
</tbody>

At the moment B2 returns the current quarter and year, which is then used as a comparison for the formula in B4.

Obviously, the formula I found using INDEX MATCH in B4 does not work, as all columns (from D4:F4) are summed up. I would need something similar, which only sums up every second columns - the YTD "plan" values (D4 and F4 in this case) and leaves out the YTD "actual" values. I unfortunately, have no clue how to insert this additional condition. The similar formula in C4 is now missing as well (value has been inserted manually).

Thank you for your help! It is highly appreciated.