Monthly Target Tracking

[JamieP89]

Hi All,

I'm wondering if the below scenario is possible?

So let's say I have the following data

Month	Month 2	Month 3	Month 4	Month 5	Month 6	Month 7	Month 8	Month 9	Month 10
Target	100,000	100,000	100,000	100,000	100,000	100,000	100,000	100,000	100,000
Actual	100,000

In the example above I want to track my target to date. Now if we look at it ourselves we know currently we are 100% to date. But when I ask excel to do a SUMIFS I have to state the range I want to calculate the sums which means the data is taking a total sum of the target and measuring it against the actual.

The question is, is there a way to as it to only measure if on a target to date RAG status that we are in fact 100% and green and not red(because the calculation thinks were only 10% target to date?

 

[bstory84]

What does your current formula look like?
In relation to the data posted where is the "Target to Date" being outputted?

Here is 1 scenario.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Month	Month 1	Month 2	Month 3	Month 4	Month 5	Month 6	Month 7	Month 8	Month 9	Month 10	Total to date
2	Target	100,000	100,000	100,000	100,000	100,000	100,000	100,000	100,000	100,000	100,000	200,000
3	Actual	100,000	100,000									200,000
4
5	%	100.00%	100.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	100.00%
Sheet1

Cell Formulas
Range	Formula
L2	L2	=SUMIF($B$3:$K$3,">0",$B$2:$K$2)
L3	L3	=SUM(B3:K3)
B5:L5	B5	=B3/B2

 

[JamieP89]

Sorry I should have been clearer, the target to date would be being output in a new column similar to your mock up. It looks as if your L2 range formula would do the trick based on the behaviour I would expect to see.

Just to be clear so that I know I understand it, essentially what the formula is saying is if B3 to K3 is above 0 then B2 to K2 (and it's only SUMMING the cells in row 3 that are filled in ??

If that is the case thank you very much @bstory84 !! Very helpful indeed!

 

[bstory84]

You're welcome! Also, Yes you are correct.
The SUMIF is just saying only add values from Row#2 if Row #3 has a value >0.

 

[JamieP89]

Glad I've understood it then Think I was just not saying that well enough in my brain to make it work!