Sum set of cells unless cell to right has value

[BeadyBob]

Hello, I am hoping to get help with a formula that will sum a set of cells, unless there is a value present in the cell to the right.
More specifically, I have a spreadsheet that lists an "estimated" number for the month followed by the "actual" number for the same month. I would like to total by quarter, the values in the estimated cell, but once values are placed in the actual cell I would like the formula to use that value instead.

I thought I was getting somewhere with this formula:
=SUM(B4="0",A4,B4)

A4 = January Estimate
B4 = January Actual

This worked well for January only. When I try to add the other months I'm not getting the result I'm after. I tried nesting etc, I'm positive I just don't know the proper way to carry this through each of the three month grouping to arrive at the total for the quarter.

Any help is very appreciated, thank you!

 

[Joe4]

Welcome to the Board!

Let's say that your data is in columns A and B in rows 4-15. Then try this formula:

=SUMIF(B4:B15,0,A4:A15) + SUM(B4:B15)

 

[BeadyBob]

That worked great, thank you! I still have quarter total that only includes January. Would you be able to tell me how to add to that formula to pull in February (columns C and D), and March (columns E and F)? My formula building skills are somewhat limited! Thanks again.

 

[Joe4]

Are you saying that your monthly data goes across row 4, instead of down columns A and B?
So is all the data you want to SUM in row 4?

It might be helpful if you can either describe in detail the layout of your data or provide a screen image.
You cannot upload files to this site. But there are tools you can use to post screen images. They are listed in Section B of this link here: http://www.mrexcel.com/forum/board-a...forum-use.html.
Also, there is a Test Here forum on this board that you can use to test out these tools to make sure they are working correctly before using them in your question.

 

[BeadyBob]

Hi, yes the data goes across row 4 not down columns, I'm sorry I wasn't clear on that. I'm unable to load an example right now, but I'll do my best to describe.

Row 1 - contains each month name twice (January A1, January B1, February C1, February D1, March E1, March F1). G1 is titled "1st Quarter"
Row 2 - contains the words estimate and actual for each Month (for January "estimate" is A2, January "actual" is B2...through March "actual" F2). G2 contains word "subtotal".
Row 3 - is blank row
Row 4 - hold the estimate and actual value for each month (A4 = Jan Estimate, B4 = Jan Actual, C4 = Feb Estimate....through F4 = March Actual). G4 is where the formula is being placed.

I hope I've explained well enough, if I need to provide more info just let me know. I truly appreciate your help.

 

[Joe4]

OK, assuming 12 months of data stretching from A4:X4, use this formula:

=SUMPRODUCT(--(MOD(COLUMN(A4:X4),2)=0),(A4:X4))+SUMPRODUCT(--(MOD(COLUMN(A4:W4),2)=1),--(B4:X4=0),(A4:W4))

 

[BeadyBob]

Genius, that works great! I was able to adjust the cell reference for each quarter. This is exactly what I needed, thank you so much!

 

[Joe4]

You are welcome.

 

[BeadyBob]

Hi Joe4,
Something new has developed and I wonder if you might help me again?

The new development is that I've been asked to add a "variance" column after each month's estimate and actual to calculate the difference. So now, after January actual and January estimate, there is January Variance...and so on for each month of the year.
The variance doesn't matter to the totals, but adding that column throws the formula off. My attempts to fix are leading nowhere.

Any advice on adjusting this formula is appreciated, and thank you for your patience.

 

[Joe4]

Since there are now three columns per month instead of 2, we need to use MOD 3 instead of MOD 2.
Assuming this now goes out to AJ4, try:

=SUMPRODUCT(--(MOD(COLUMN(A4:AJ4),3)=2),(A4:AJ4))+SUMPRODUCT(--(MOD(COLUMN(A4:AI4),3)=1),--(B4:AJ4=0),(A4:AI4))