Using Nested IF to calculate open items as of a reporting period

Tracy.raggins34306

My Smartsheet tracks orders. I need to calculate the number of open orders as of a particular month. For example, how many open orders as of January or February and the amount is over 5k. Additionally I need to count orders that were closed after the reporting month. In other words, still open as of January even if closed in February.

 

Here is my logic:

 

If status is open then countif order received in the current month and year and is over 5K 

and

If status is closed then countif order received prior to the current month or after the current month and over 5k.

 

I've created a nested IF formula which is not working.

 

=IF(Status:Status ="Open",COUNTIFS([Date Rec'd]:[Date Rec'd],<=varReportEnd1,[Claim Amount]:[Claim Amount],>=5000),IF(Status:Status="Closed",COUNTIFS([Date Rec'd]:[Date Rec'd],<=varReportEnd1,Amount:Amount,>=5000,[Closed Date]:[Closed Date]>varReportEnd1))

 

 

VarReportStart- 01/01/18

VarReportEnd=01/31/18

 

What's missing?

 

Mike Wilday

Without seeing the data it is hard to know what is going on... but it seems like you could make Status:Status ="Open" a part of the Countifs requirements. Another issue might be that your trying to run an if statement based on an entire column, I don't know what the result of status:status = "open" would result it. Could it be looking to see if EVERY cell is open? you might try either including that as part of your countifs, or break down your formula to calculate for a row at a time. 

Have you tried breaking down your formula into 2 to see if the first half works? Try separating out each part and test it by itself to see if you get results. 

Start with: =IF(status:status = "Open", "Test") if you get the word Test in your result then you know that the range test for open is working. 

Then I would run a test on each of your CountIfs statements to make sure they work by themselves.