Dear All,

I should be grateful if you would, please, help me with Excel formula that is returning either #VALUE! in a ‘Total’ cell or incorrect addition/subtraction result when any one of the cells is blank.

I have attached a sample file showing an example of what I am doing, with an explanation below of the difficulty that I am having, and for which I need your kind help.

1. From the Sample file, in Cell P90, I have the following formula=N86which returns a value.

2. In Cell P91, I also have the following formula which returns a value or the cell is blank if there is no value to return:

‘=IF(SUMPRODUCT(--(AM6:AM85=AH86),E6:E85)=0,"",SUMPRODUCT(--(AM6:AM85=AH86),E6:E85))

3. For Cell P92, which gives the result of subtracting cell P91 from cell P90, I have the following formula:‘=IFERROR(P90-P92,"")

4. When there are numbers in both P90 and P91, cell P92 returns a correct balance.

5. However, the following problems arise with the formula in cell P92 (the 'Total' cell):

a) If Cell P90 is blank but with a number in Cell P91 e.g. £300, Cell P92 becomes blank. Under this circumstance, I would expect P92 to show “-£300” i.e. minus £300.

b) Equally, if there is a number in Cell P90, e.g. £500 but with P91 being blank, again, P92 becomes blank instead of £500 in this circumstance (£500 minus zero) should be £500 and not zero!.

When I tried altering the formula in Cell P92, I get #VALUE!, and when I use the formula shown, I get incorrect answers. I must be doing something wrong wrong with the formula in Cell P92.

I need help, please, with the correct formula for Cell P92 to cater for the two scenarios in (a) and (b) above.

Thanks all for your help.

Buddy8

## Bookmarks