Sum numbers using two row and sum the numbers greater then 0 using row 1 as the default

[frankday]

I have row 1 (2019) totals Row 2 (2018) totals. Row 2 has all 12 months worth of data. Row 1 has 9 months worth of data. I need the sum of all 9 months in row 1 and the last 3 of row 2. That easy enough. Now when I collect the totals for the 10th month of row 1 I then need the total for months 1 - 10 of row 1 and month 11-12 of row 2. How can I do with without creating 12 different if statements.

 

[DRSteele]

You can try this. Formula R6 sums the current row's monthly values and uses the prior year's values for empty months.


[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]

Book1
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
5		1	2	3	4	5	6	7	8	9	10	11	12	Sum_row	Sum_Projected
6	2019	100	100	100	100	100	100	100	100	100				900	930
7	2018	10	10	10	10	10	10	10	10	10	10	10	10	120
Sheet43

Cell Formulas
Range	Formula
Q6	=SUM(E6:P6)
Q7	=SUM(E7:P7)
R6	=SUM(IF(E6:P6,"",OFFSET(E6:P6,1,)))+SUM(E6:P6)

[/FONT]

 

[frankday]

Perfect, Thanks

 

[DRSteele]

You're welcome.