Need SUMIF/SUMPRODUCT formula with criteria in non-consecutive columns

[sakinahobbes]

Excel masters, I need your help! I'm doing a monthly variance analysis and need a year-to-date formula that will calculate only columns that have monthly actuals populated in them. For example, I have 3 columns for each month (actuals/budget/variance) and a total year-to-date at the end (actuals/budget/variance). I don't want to have to adjust the YTD column formulas at the end of each month close, how can I get a formula that will total my 3 columns (actuals/budget/variance) for only the months that have amounts in the "actuals" column.


For example:
19-Jan 19-Jan 19-Jan 19-Feb 19-Feb 19-Feb 19-Mar 19-Mar 19-Mar YTD YTD YTD
GL code Actuals Budget Variance Actuals Budget Variance Actuals Budget Variance Actuals Budget Variance
1111 1000 1000 1000 ? 3000 ?
2222 2000 2000 2000 ? 6000 ?
3333 3000 3000 3000 ? 9000 ?


If I have actuals for both Jan and Feb (but not March as the month hasn't closed yet), what formula would I put in the YTD actuals/budget/variance columns so that it would only include Jan and Feb but not the march budget (3000). Please tell me I do not need a VBA. Thanks in advance!!!

 

[jim may]

Cells in Either Column O or P (O currently used) determine the Number of Months WITH $$'s.
Other Formulas in YTD are just nested IF Statements contingent on Column O number.



Excel 2010

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
YTD	YTD	YTD	Or Alternate Formula
Actuals	Budget	Variance	Actuals	Budget	Variance	Actuals	Budget	Variance	Actuals	Budget	Variance
?	?
?	?
?	?

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]19-Jan[/TD]
[TD="align: right"]19-Jan[/TD]
[TD="align: right"]19-Jan[/TD]
[TD="align: right"]19-Feb[/TD]
[TD="align: right"]19-Feb[/TD]
[TD="align: right"]19-Feb[/TD]
[TD="align: right"]19-Mar[/TD]
[TD="align: right"]19-Mar[/TD]
[TD="align: right"]19-Mar[/TD]

[TD="align: center"]# of PeriodsWith Dollars[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]GL[/TD]
[TD="align: center"]code[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1111[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]1000[/TD]

[TD="align: right"]3000[/TD]
[TD="align: right"]100[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"]1100[/TD]
[TD="align: right"][/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]2222[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]2000[/TD]

[TD="align: right"]6000[/TD]
[TD="align: right"]200[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]8000[/TD]
[TD="align: right"]2200[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]3333[/TD]
[TD="align: right"]3000[/TD]
[TD="align: right"]3000[/TD]
[TD="align: right"]3000[/TD]

[TD="align: right"]9000[/TD]
[TD="align: right"]300[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]12000[/TD]
[TD="align: right"]3300[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]L3[/TH]
[TD="align: left"]=IF($O3=1,C3,IF($O3=2,SUM(C3,F3),SUM(C3,F3,I3)))[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]M3[/TH]
[TD="align: left"]=IF($O3=1,D3,IF($O3=2,SUM(D3,G3),SUM(D3,G3,J3)))[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]O3[/TH]
[TD="align: left"]=COUNT($C3,$F3,$I3)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]P3[/TH]
[TD="align: left"]=COUNTA($C3,$F3,$I3)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]