if the answer is zero

Whats the correct way to do this?

=if(SUMIFS({Service Projects Tracking Master Log Range 1}, {Service Projects Tracking Master Log Range 2}, AND(@cell >= DATE(2021, 8, 1), @cell <= DATE(2021, 8, 30)),0,75,000)

Answers

  • Stefan
    Stefan ✭✭✭✭✭✭

    Hello @Samuel Dowdy Jr,

    would you mind to describe in words what you want to achieve and what the expected result is?

    Greetings

    Stefan

    Smartsheet Consulting, Solution Building, Training and Support.

    Projects for Processes and for People.

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Samuel Dowdy Jr

    I agree with Stefan, it would be helpful to know what you want to see. It sounds like you want to know IF the answer is 0, then ... something.

    So:

    =IF(SUMIFS(Formula) = 0, then this, otherwise that)

    For example:

    =IF(SUMIFS({Service Projects Tracking Master Log Range 1}, {Service Projects Tracking Master Log Range 2}, @cell >= DATE(2021, 8, 1), {Service Projects Tracking Master Log Range 2}, @cell <= DATE(2021, 8, 30)) = 0, 75, 000)

  • Samuel Dowdy Jr.
    Samuel Dowdy Jr. ✭✭✭✭✭

    If the amount is in June it will equal the amount, if it isn't and the total is zero the the amount will be 75,000

  • Samuel Dowdy Jr.
    Samuel Dowdy Jr. ✭✭✭✭✭

    I want it to add range 1. If the answer is zero then 75,000 should be the answer


    the formula you gave me makes the answer zero all the time

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Samuel Dowdy Jr

    Thank you for clarifying! Commas in formulas split up logic statements, so we will need to represent 75,000 as 75000.

    So you want the formula to say this:

    If the SUMIFS is 0, then 75000, otherwise return the result of the SUMIFS.

    Try this:

    =IF(SUMIFS({Service Projects Tracking Master Log Range 1}, {Service Projects Tracking Master Log Range 2}, @cell >= DATE(2021, 8, 1), {Service Projects Tracking Master Log Range 2}, @cell <= DATE(2021, 8, 30)) = 0, 75000, SUMIFS({Service Projects Tracking Master Log Range 1}, {Service Projects Tracking Master Log Range 2}, @cell >= DATE(2021, 8, 1), {Service Projects Tracking Master Log Range 2}, @cell <= DATE(2021, 8, 30)))

    Let me know if this works for you!

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!