• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula to extract totals based on "New forecast" of "To be Raised" Criteria


I had budget table where budget total and actual are tracked. There are few different scenarios in this table attached:

1. Item was budgeted, Contract renewed, and PO Closed. Item 1 in the file. - No issues in this case, straight forward
2. Item was budgeted, Contract NOT renewed, however, I have a forecast price for renewal from the vendor. The shopping cart is not Raised yet as there are approval needed. Item 2 in the file
3. Item was not budgeted. New item purchased this year. Shopping cart raised and PO Closed - item 3 in the file
4. Item was budgeted, PO was issued and closed. Item 4 in the file
5. Item was not budgeted. New item to be purchased this year. However, no Shopping cart raised. Only has a forecast of the price. Pending approval. Item 5
6. Similar to 2 above.

In the summary table, I need to pull the information for Budget total, Actual total, SC "To be Raised", and New Forecast

Budget and Actual total are straight forward. But for the To be Raised and New forecast, I need to ensure that there is no double dipping, meaning, if an item is budgeted, but shopping cart not raised, but I have a purchase forecast $, then I need to ensure that only the New forecast amount. But if there is no New forecast amount then it should give me the Budget total.

And for New Forecast, only items that are New and the forecast total for those item.

I hope the attached file would explain things clearly. Thank you in advance.