0

Below is part of a financial worksheet to calculate profit and loss.

Currently C19 shows £ 0.00 as there is no data in the next to calculate an answer.

I want cell C19 to return blank until such a point there is data in the next sheet to calculate an answer but NOT using custom number formats (as I am already using one that I would like to keep)

Custom number format already in use in cell C19 : + £ #,##0.00;- £ #,##0.00; 0.00

This is currently the formula in cell C19 : =SUMIF('SPREADS LOG'!A:A,"*OPEN*",'SPREADS LOG'!K:K)

Can anyone suggest the correct formula needed?

Insider
  • 95
  • 12
  • 2
    Maybe `=IF(SUMIF('SPREADS LOG'!A:A,"*OPEN*",'SPREADS LOG'!K:K)=0,"",SUMIF('SPREADS LOG'!A:A,"*OPEN*",'SPREADS LOG'!K:K))`? – VBasic2008 Apr 01 '21 at 11:11
  • 1
    Check if there is **any** entry by using `COUNTIF` or `COUNTIFS` – Ron Rosenfeld Apr 01 '21 at 11:16
  • @VBasic2008 perfect solution. Can the same be done with ```=TEXT(SUM('SPREADS LOG'!M:M)," £ #,##0.00") & " / " & TEXT(SUM('SPREADS LOG'!K:K)," £ #,##0.00")``` ? – Insider Apr 01 '21 at 11:34

3 Answers3

1

You can move the number format to TEXT formula:

=TEXT(SUMIF('SPREADS LOG'!A:A,"*OPEN*",'SPREADS LOG'!K:K);"+ £ #,##0.00;- £ #,##0.00; ")

There is a optimization problem using

IF(SUMIF(...)=0,",SUMIF())

since you'll end calculate the sum twice.

Not just for fun
  • 39
  • 4
1

I see 4 solutions:

  1. Change your cell formating to: + £ #,##0.00;- £ #,##0.00;
  2. Change the formula as the previous answer, but if you have 365 you can use the new LET function to calculate the SUMIF only once:
    =LET(mySUMIF,IF(SUMIF('SPREADS LOG'!A:A,"OPEN",'SPREADS LOG'!K:K)=0,"",SUMIF('SPREADS LOG'!A:A,"OPEN",'SPREADS LOG'!K:K)),IF(mySUMIF=0,"",mySUMIF))
  3. Use Conditional formatting to have the same color of the cell as the background.
  4. Change the Excel setting to not display any 0 values:
    File > Options > Advanced
    Select your worksheet and then unselect the option Show a zero in cells that have zero value.
Florin
  • 375
  • 3
  • 13
  • Hi @Florin, thanks for your answer. I used Vbasic2008's solution in his comment! Again thank you for help. – Insider Apr 04 '21 at 14:41
0

To avoid changing formatting my go to when hiding zeros is to rely on the fact that divide by zero will produce an error. So =IFERROR(1/(1/value),"") will produce blanks if value is ever zero and will otherwise produce the value. It avoids typing in a separate check for zero entries.

In the case in question it would just be:

=IFERROR(1/(1/SUMIF('SPREADS LOG'!A:A,"*OPEN*",'SPREADS LOG'!K:K)),"")
Fedor
  • 17,146
  • 13
  • 40
  • 131
Oscar
  • 1