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.

HELP!!! I am working on a nested IF formula that just doesn't work, any help is appreciated.

Elaine Byman
edited 12/09/19 in Archived 2016 Posts

I am trying to calculate the total revenue per month as well as the Margin % per month from our Sales Pipeline. Some of the projects run into the following month so we need to see what the revenue and margin are for each part of that time frame.

I was able to get the formula to work for the margin % but only for one cell reference and the start/end dates fall within July.  I need to calculate all data within the row and to calculate what % is July and what would be Aug for those projects that run into the next month.

The formula that worked for the single cell reference looks like this =IF(MONTH([Start Date]12) = 7, IF(MONTH([End Date]12) = 7, Percent12))


What I think it should look like is this =IF(MONTH([Start Date]:[Start Date]) = 7, IF(MONTH([End Date]:[End Date]) = 7, Percent:Percent))  However when this is entered I get an error (#Missing or invalid parameters)

Any help is appreciated!



  • Greg Gates
    Greg Gates ✭✭✭✭✭
    edited 07/14/16

    Hello Elaine!


    You're receiving that error because the MONTH function can only take a single cell as input. I don't think I fully understand your situation, but could you maybe try comparing the the months of the start and end dates, and then change your calculations if those months are different? For example,


    IF (MONTH([Start Date]1) <> MONTH([End Date]1), <calculate percent for different months here>, <calculate percent for same month here>)

  • Thank you, I can give that a try and see what I get.

  • Greg I tried your suggestion however I am not certain I know what information should be entered in <calculate percent for different months here>, <calculate percent for same month here>).  Are you referring to the column and row location of where I want that data to appear?  If so I did try that and it didn't work either.

    Would I be able to share my sheet with you so you can see what I'm trying to acheive and understand the issue I have with the formula?

  • Greg Gates
    Greg Gates ✭✭✭✭✭

    Sure, we could try that! My email is [email protected]

This discussion has been closed.