Index Collect with If Statement

brhea110891

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

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.