Excel formula to return blank instead of zero

[SamCha]

Hi all,

I want the cell to return blank instead of zero where the cell has no values. I am using the below IF function for the solution. I need to use the SUMIF function as I am finding out the end balance of debit and credit accounts. Are u able to suggest an easier way to return blank please.

=IF(SUMIF($C$4:$C$11,A13,$B$4:$B$11)-SUMIF($A$4:$A$11,A13,$B$4:$B$11) =0,"",SUMIF($C$4:$C$11,A13,$B$4:$B$11)-SUMIF($A$4:$A$11,A13,$B$4:$B$11))

Thanks

 

[jasonb75]

One way that comes to mind

=IFERROR(1/(1/(SUMIF($C$4:$C$11,A13,$B$4:$B$11)-SUMIF($A$4:$A$11,A13,$B$4:$B$11))),"")

Possibly this will also work if I'm following the logic correctly

=IFERROR(1/(1/SUMIFS($B$4:$B$11,$C$4:$C$11,A13,$A$4:$A$11,"<>"&A13)),"")

 

[Peter_SSs]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

For example, IF you have Excel 365 with the LET function, then you could also try

=LET(si,SUMIF($C$4:$C$11,A13,$B$4:$B$11)-SUMIF($A$4:$A$11,A13,$B$4:$B$11),IF(si=0,"",si))

 

[SamCha]

One way that comes to mind

=IFERROR(1/(1/(SUMIF($C$4:$C$11,A13,$B$4:$B$11)-SUMIF($A$4:$A$11,A13,$B$4:$B$11))),"")

Possibly this will also work if I'm following the logic correctly

=IFERROR(1/(1/SUMIFS($B$4:$B$11,$C$4:$C$11,A13,$A$4:$A$11,"<>"&A13)),"")

Thanks a lot Jasonb75 the first one worked really well for me. The second function didnt work as my end balance changed.

 

[SamCha]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

For example, IF you have Excel 365 with the LET function, then you could also try

=LET(si,SUMIF($C$4:$C$11,A13,$B$4:$B$11)-SUMIF($A$4:$A$11,A13,$B$4:$B$11),IF(si=0,"",si))

Thanks a lot Peter. I have updated my account details as per your suggestion. I have 2016 version.

 

[Peter_SSs]

I have updated my account details as per your suggestion.

Thanks for doing that.