Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

How do i return.....?

edited 12/09/19

How do i get [Current Month Sales] to return a (blank cell) instead of \$0.00

<IFRAME WIDTH=1000 HEIGHT=700 FRAMEBORDER=0 SRC="https://app.smartsheet.com/b/publish?EQBCT=fccbf4d16b7442f9bd0160a985e4e494"></IFRAME>

• Employee

Hi Heath,

I've modified your formula to produce the result you're looking for (look in [Current Month Sales]2 on your sheet). To be frank, the formula is nasty:

=IF(SUMIFS([Value of Jobs]2, [Sold By]2, "Heath Sanders", Status2, "WON", Result2, 1, [Sold Date]2, IFERROR(MONTH(@cell), "") = MONTH(TODAY()), [Sold Date]2, IFERROR(YEAR(@cell), "-") = YEAR(TODAY())) = 0, "", SUMIFS([Value of Jobs]2, [Sold By]2, "Heath Sanders", Status2, "WON", Result2, 1, [Sold Date]2, IFERROR(MONTH(@cell), "") = MONTH(TODAY()), [Sold Date]2, IFERROR(YEAR(@cell), "-") = YEAR(TODAY())))

To break it down: IF this formula returns 0, return blank "", otherwise, run the same formula.

• edited 02/16/17

Thanks, Shaine,

I'm keeping records of all formulas, in case my computer decides its smart enough to tell me what im having for breakfast in the morning and I need to make the relevant changers lol

Nasty good? or nasty you would make some changes to formula to get the same result ?

•

Thanks, Shaine,

I'm keeping records of all formulas, in case my computer decides its smart enough to tell me what im having for breakfast in the morning and I need to make the relevant changers lol

Nasty good? or nasty you would make some changes to formula to get the same result ?

• Employee

Nasty as in: I believe it's the only way to accomplish what you need but the formula is "expensive" in terms of processing.

The new formula is now twice as long. Down the road, this could cause slowness when in a sheet that has this formula duplicated down a column hundreds of times.

This discussion has been closed.