Hi,

I would like to return 'no result' if the cells in the sum below are empty.

=sum(a5:a9)+(a4*3)

I think I need an if function in the sum above so if all cells
A4,A5,A6,A7,A8 & A9 have no values in then give 'no result''. If any of these
cells have a value which may come to 0 then give 0 or any other value of the
sum.

hope this clear I can't get this to work

Thanks

If you meant display "no result" if ALL cells are empty:
=IF(SUMPRODUCT(--ISBLANK(A4:A9))=5,"no result",SUM(A5:A9)+A4*3)

If you want to display "no result" if ANY of the cells are empty:
=IF(SUMPRODUCT(--ISBLANK(A4:A9))0,"no result",SUM(A5:A9)+A4*3)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Haz" wrote:

Hi,

I would like to return 'no result' if the cells in the sum below are empty.

=sum(a5:a9)+(a4*3)

I think I need an if function in the sum above so if all cells
A4,A5,A6,A7,A8 & A9 have no values in then give 'no result''. If any of these
cells have a value which may come to 0 then give 0 or any other value of the
sum.

hope this clear I can't get this to work

Thanks

If any of these cells have a value which may
come to 0 then give 0

Not real sure what "which may come to 0" means.

See if this does what you want:

=IF(COUNT(A5:A9)=0,"No Result",IF(COUNTIF(A5:A9,0),0,SUM(A5:A9,A4*3)))

--
Biff
Microsoft Excel MVP


"Haz" wrote in message
...
Hi,

I would like to return 'no result' if the cells in the sum below are
empty.

=sum(a5:a9)+(a4*3)

I think I need an if function in the sum above so if all cells
A4,A5,A6,A7,A8 & A9 have no values in then give 'no result''. If any of
these
cells have a value which may come to 0 then give 0 or any other value of
the
sum.

hope this clear I can't get this to work

Thanks

Hi

=IF(COUNT(A5:A9,A4)0,SUM(A5:A9,3*A4),"no result")

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Haz" wrote:

Hi,

I would like to return 'no result' if the cells in the sum below are empty.

=sum(a5:a9)+(a4*3)

I think I need an if function in the sum above so if all cells
A4,A5,A6,A7,A8 & A9 have no values in then give 'no result''. If any of these
cells have a value which may come to 0 then give 0 or any other value of the
sum.

hope this clear I can't get this to work

Thanks

Hi Thanks for coming back to me,

The first Sumproduct function works perfectly but only if A4:A9 are blank. I
have formulas in a5:a9 and they display £0.00 if nothing is entered or 0.00.

Can this be adapted to except £0.00 as 0 values to display 'no result'?

Thanks again

"Luke M" wrote:

If you meant display "no result" if ALL cells are empty:
=IF(SUMPRODUCT(--ISBLANK(A4:A9))=5,"no result",SUM(A5:A9)+A4*3)

If you want to display "no result" if ANY of the cells are empty:
=IF(SUMPRODUCT(--ISBLANK(A4:A9))0,"no result",SUM(A5:A9)+A4*3)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Haz" wrote:

Hi,

I would like to return 'no result' if the cells in the sum below are empty.

=sum(a5:a9)+(a4*3)

I think I need an if function in the sum above so if all cells
A4,A5,A6,A7,A8 & A9 have no values in then give 'no result''. If any of these
cells have a value which may come to 0 then give 0 or any other value of the
sum.

hope this clear I can't get this to work

Thanks