Sum

[basia]

1	A	B	C	D
2		January	February	March
3	Accrual	1,075.00	980.00	1,050.00
4	Actual	1,040.30	979.52
5	Variance	(34.70)	(0.48)	1.10

<colgroup><col><col><col span="3"></colgroup><tbody>
</tbody>

In this example I need a formula that totals actual values (B4, C4) unless the actual value is blank in which case I need to add the accrual (March D3 in this example).

I would need this formula to apply the same way to each month. So if there is no actual value then using the accrual amount. Ex: If Feb actual is blank, total should equal January actual and Feb accrual.

 

[Andrew Poulsom]

Welcome to MrExcel.

Is it?

=SUM(B4,IF(C4="",D3,C4))

 

[collin]

This might work - it sums everything in the Actual row, then adds the cells in the Accrual row when the Actual row below it is blank

=SUM(B3:D3)+SUMIF(B3:D3,"",B2:D2)

 

[pjmorris]

not sure I've got this right, but does this work:

=SUMPRODUCT(--(LEN(B3:I3)=0),(B2:I2))+SUM(B3:I3)

It assumes you have additonal months after March but should work even if they're all blank.

 

[bbott]

Maybe:
=SUM(B3:M3,INDEX(B2:M3,1,MATCH(9.99999999999999E+307,A3:M3)))

Assuming Jan through Dec in columns B through M.

 

[basia]

not sure I've got this right, but does this work:

=SUMPRODUCT(--(LEN(B3:I3)=0),(B2:I2))+SUM(B3:I3)

It assumes you have additonal months after March but should work even if they're all blank.

This worked!!! Thank you so much!!!!

 

[pjmorris]

my pleasure, welcome to the forum and many thanks for the feedback.

Regards