Index Collect with If Statement

Can you please help me write a formula where I am doing an Index Collect from another sheet with an If statement. Maybe this will help explain...

This is my current formula: =INDEX(COLLECT({PM Weekly Allocation Week hours}, {PM Weekly Allocation Project Name}, [Project Name]@row), 1)

This works fine, but I want to collect the hours IF the date in the column "Weekly Date Start" in the sheet "PM Weekly Allocation" is a specific date I specify. So for example, If I want to collect the hours reported on 04/24/23, how would I write the formula?

Also, how do I add to the formula if I want to add another match? For Example, in addition to the Project name matching, the assigned Project Manager also match.

Thank you

Becky

Answers

  • AravindGP
    AravindGP ✭✭✭✭✭✭

    Hi Becky,


    You can add the If statement before the index/collect. So your formula would be =IF(HAS(Range of the column that has Weekly Date Start, "04/24/23"), INDEX(Collect({PM Weekly Allocation Week hours}, {PM Weekly Allocation Project Name}, [Project Name]@row), 1)). This will collect the total time clocked against 04/23/23 as a date in the sheet.

    Thanks,

    Aravind GP| Principal Consultant

    Atturra Data & Integration

    M: +61493337445

    E:Aravind.GP@atturra.com

    W: www.atturra.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!