CODEWITHSUNDEEP
excel
Hussain Juma
Hussain Juma

Reputation: 3

If this cell equals 0 then back to blank

I need help to make the cell blank if it equal 0

I used this in cell

=SUMIF(Jan!D:D,A4,Jan!E:E)+SUMIF(Feb!D:D,A4,Feb!E:E)+SUMIF(Mar!D:D,A4,Mar!E:E)+SUMIF(Apr!D:D,A4,Apr!E:E)+SUMIF(May!D:D,A4,May!E:E)+SUMIF(Jun!D:D,A4,Jun!E:E)+SUMIF(Jul!D:D,A4,Jul!E:E)+SUMIF(Aug!D:D,A4,Aug!E:E)+SUMIF(Sep!D:D,A4,Sep!E:E)+SUMIF(Oct!D:D,A4,Oct!E:E)+SUMIF(Nov!D:D,A4,Nov!E:E)+SUMIF(Dec!D:D,A4,Dec!E:E)

Upvotes: 0

Views: 683

Answers (1)

Skin
Skin

Reputation: 11277

There are a few ways I can think of ...

Method 1 - IF statement (not ideal given the length of your formula, but it works)

=IF(SUMIF(Jan!D:D,A4,Jan!E:E)+SUMIF(Feb!D:D,A4,Feb!E:E)+SUMIF(Mar!D:D,A4,Mar!E:E)+SUMIF(Apr!D:D,A4,Apr!E:E)+SUMIF(May!D:D,A4,May!E:E)+SUMIF(Jun!D:D,A4,Jun!E:E)+SUMIF(Jul!D:D,A4,Jul!E:E)+SUMIF(Aug!D:D,A4,Aug!E:E)+SUMIF(Sep!D:D,A4,Sep!E:E)+SUMIF(Oct!D:D,A4,Oct!E:E)+SUMIF(Nov!D:D,A4,Nov!E:E)+SUMIF(Dec!D:D,A4,Dec!E:E) = 0, "", =SUMIF(Jan!D:D,A4,Jan!E:E)+SUMIF(Feb!D:D,A4,Feb!E:E)+SUMIF(Mar!D:D,A4,Mar!E:E)+SUMIF(Apr!D:D,A4,Apr!E:E)+SUMIF(May!D:D,A4,May!E:E)+SUMIF(Jun!D:D,A4,Jun!E:E)+SUMIF(Jul!D:D,A4,Jul!E:E)+SUMIF(Aug!D:D,A4,Aug!E:E)+SUMIF(Sep!D:D,A4,Sep!E:E)+SUMIF(Oct!D:D,A4,Oct!E:E)+SUMIF(Nov!D:D,A4,Nov!E:E)+SUMIF(Dec!D:D,A4,Dec!E:E))

Method 2 - Conditional Formatting (may not suit your requirement)

You can always conditionally format the cell if the value is 0.

CF

Method 3 - Worksheet Options (Could be the best option?!?)

Change the option as shown below in the preferences for the worksheet.

Options

Upvotes: 1

Related Questions