Sum cell in every nth column only if adjacent cell is not blank

[Karlab]

I am using the following formula to add my actual spending at every 2nd column for my YTD actual spending:

=SUMPRODUCT((MOD(COLUMN($B2:$E2),2)=0)*($B2:$E2)) In the YTD Budget column, I want to add the Budget-Jan and Budget-Feb cells ONLY if the Actual cell for that month (cell to left) is not blank. (So result for YTD Budget in top cell would be 4.) Can anyone help me with a formula for this? FYI, I don't want to convert my spreadsheet to a Table. Thank you!

Category	Actual-Jan	Budget-Jan	Actual-Feb	Budget-Feb	YTD Actual	YTD Budget
Bonus	2	4		4	2
Interest Inc	4	4		4	4
Other Inc	6	4		4	6
Net Salary Dave	2	4		4	2
Net Salary Karla	2	4		4	2

 

[steve the fish]

You could use offset function or with the way yours is setup you can actually feed the formula an offset range:

=SUMPRODUCT((MOD(COLUMN($B2:$E2),2)=1)*($B2:$E2)*($A2:$D2<>""))

 

[jtakw]

Hi,

You don't need SUMPRODUCT for either column, a simple SUMIF will do the job:

Book3.xlsx
	A	B	C	D	E	F	G
1	Category	Actual-Jan	Budget-Jan	Actual-Feb	Budget-Feb	YTD Actual	YTD Budget
2	Bonus	2	4		4	2	4
3	Interest Inc	4	4		4	4	4
4	Other Inc	6	3		4	6	3
5	Net Salary Dave	2	4		4	2	4
6	Net Salary Karla	2	4		4	2	4
Sheet831

Cell Formulas
Range	Formula
F2:F6	F2	=SUMIF(B$1:E$1,"Actual*",B2:E2)
G2:G6	G2	=SUMIF(B2:D2,"<>",C2:E2)

 

[steve the fish]

That G2 one wouldnt work Jtakw. You could do it with a sumifs mind.

 

[jtakw]

That G2 one wouldnt work Jtakw. You could do it with a sumifs mind.

Wait, checking...

 

[Karlab]

Thank you, Steve and Jtakw! Steve, yours worked great on my sample spreadsheet but my own formula (and the one you built off it) failed on my much more complex real budget spreadsheet. Sorry, I should have validated that first. Are you able to show me how to solve it with the OFFSET function?

(Jtakw, The SUMIF didn't work. I had already tried using the SUMIFS but I had trouble pasting it as the Shift, control, enter did nothing when I tried to copy it into the other cells and I don't want to convert our budget spreadsheet to a table.)

 

[jtakw]

Corrected below:

Try it.

Book3.xlsx
	A	B	C	D	E	F	G
1	Category	Actual-Jan	Budget-Jan	Actual-Feb	Budget-Feb	YTD Actual	YTD Budget
2	Bonus	2	4		4	2	4
3	Interest Inc	4	4		4	4	4
4	Other Inc	6	3	2	4	8	7
5	Net Salary Dave	2	4		4	2	4
6	Net Salary Karla	2	4		4	2	4
Sheet831

Cell Formulas
Range	Formula
F2:F6	F2	=SUMIF(B$1:E$1,"Actual*",B2:E2)
G2:G6	G2	=SUMIFS(C2:E2,B2:D2,"<>",C$1:E$1,"Budget*")

NOTE: Normally entered, Not CSE

 

[Karlab]

Thank you, Jtakw! That worked great! I realized that I had to take out some blank columns my husband had in our budget spreadsheet that made it look pretty but were interfering with the formulas you suggested! (That might also have interfered with Steven's solution too but I didn't go back to re-do and check.) So relieved to have it solved!

 

[jtakw]

You're welcome, glad it worked for you, thanks for the feedback.