Help with Formula Vlookup, collect, if?

Options

Not sure if this is possible but I have a sheet to track monthly billing and another sheet to forecast milestone completions.

On the monthly billing sheets I want it to look at the milestone forecasting sheet and if a milestone is forecasted to complete in a month add a billings to the billing sheet.

Billing sheet looks like this:

For Test customer in each monthly Column I want it to look at the milestone sheet and if the project has a milestone dated for the referenced month pull in the correct billing.

This is the Milestone sheet for the customer:


This customer closes phase 3 in may 2023 so in the billing sheet I want to see nothing for Jan - april but in the May column it should pull in $11,512.50, and then in July $9210 so on and so forth and I have no idea where to even start

Tags:

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    If you insert a hidden helper column on the source sheet that pulls the customer name onto every row, you should be able to use a SUMIFS where we grab the amount and match on the customer as well as the year/month within the date.

    =SUMIFS({Source Sheet $$ Column}, {Source Sheet Helper Column}, @cell = [Customer Name]@row, {Source Sheet Date Column}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2023))

  • CDS
    CDS ✭✭✭✭
    Options

    Ok so here is the new forecasting sheet:

    and here is my formula : =SUMIFS({Forecasting Testing Billings}, {Forecasting Customer Name}, @cell = [Customer Name]@row, {{Forecasting Current Dates}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2023))

    Where Forecasting Testing billings = line number column

    Forecasting Customer Name = Name Column

    Forecasting Current Dates = Current Dates Column

    and I get in my billings table:


    but I don't want it to add numbers so not sure if I should be summing. In the billings table if the forecasting has a date of 5/26/23 for instance for that customer for May I just want it to pull the corresponding billing in the same row of 11,512.50

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    That particular error comes from a cross sheet reference that was not created properly.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!