What function can i use for column labels with Part common title e.g. Plan in (Plan Q1, for Plan Q2, Plan Q3 and Plan Q4)?

[ronin83]

What function or formula can i use for column labels with Part common title e.g. Plan in (Plan Q1, for Plan Q2, Plan Q3 and Plan Q4)?

 

[Andrew Poulsom]

What do you want the formula to return?

 

[ronin83]

My question is part of this query.

I have twenty four columns Plan Q1, Actual Q1, Plan Q2, Actual Q2.....Actual Q12.

a.If there is data in any of the Actual columns then choose Actual column over the adjacent Plan column on the left.
b.If not, then data from Plan column.

So, in total we will have 12 columns and the 25th column should give the summation of these 12 columns, which is the goal.

 

[Snakehips]

If I understand correctly then for example assuming Plan Q1 in column A Actual Q1 in column B etc to Plan Q12 in W Actual Q12 in X then maybe......

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1	P Q 1	A Q1	P Q 2	A Q2	P Q 3	A Q3	P Q 4	A Q4	P Q 5	A Q5	P Q 6	A Q6	P Q 7	A Q7	P Q 8	A Q8	P Q 9	A Q9	P Q 10	A Q10	P Q 11	A Q11	P Q 12	A Q12	SUM
2	1	2	3		5		7		9	10	11	44	13	14	15	16	17	18	19	35	21		23	24	199
Sheet21

Cell Formulas
Range	Formula
Y2	=SUMPRODUCT(A2:X2*(ISEVEN(COLUMN(A2:X2))))+SUMPRODUCT(ISODD(COLUMN(A2:W2))*(B2:X2="")*A2:W2)

Hope that helps.

 

[ronin83]

Thanks, the logic used in the second part is pretty good.