error problem with #N/A

[brianaclark1967]

Afternoon all,

Having an issue with a formula I'm hoping you can help with.

Formula below.

=SUM(SUM(H23:H40)-SUM(M24+M25))

Column H contains some cells with #N/A so as a result the answers is coming up as #N/A, instead of the sum of the cells that contain a number.

H23:H31 contains numeric values, H32:H40 contains the error #N/A

regards

Brian

 

[Luke M]

If you have XL 2010+, you can use:
=AGGREGATE(9,6,H23:H40)-M24-M25

AGGREGATE is a new function that can be coded to ignore error values.

Alternatively, if all the values in H23:H40 are greater than 0, you could do:
=SUMIF(H23:H40,">0")-M24-M25

 

[Asheesh]

Here are couple of methods

Non Array: SUMIF(H23:H40,"<>#N/A")-(M24+M25)

Array: SUM(IFERROR(H23:H40,0))-(M24+M25)

 

[brianaclark1967]

If you have XL 2010+, you can use:
=AGGREGATE(9,6,H23:H40)-M24-M25

AGGREGATE is a new function that can be coded to ignore error values.

Alternatively, if all the values in H23:H40 are greater than 0, you could do:
=SUMIF(H23:H40,">0")-M24-M25

Hi Luke,

Thank you for your assistance, much appreciated.

Regards,

Brian

 

[brianaclark1967]

Here are couple of methods

Non Array: SUMIF(H23:H40,"<>#N/A")-(M24+M25)

Array: SUM(IFERROR(H23:H40,0))-(M24+M25)

Hi Asheesh,

Thank you for your assistance, much appreciated.

Regards,

Brian