Forum Discussion
Aggregate Index AVERAGE/SUM
Haetrox Have a look at the attached workbook. If I understood your intentions correctly, you may use SUMIF in column N on the Calculation sheet. No need for such a complicated AGGREGATE/INDEX/ MATCH function.
Couldn't replicate the #VALUE error you mentioned. See cell M20 on the Calculation sheet.
Lastly, you mention that you want drop-down lists for the names. You need to create a list with all possible names first and refer to that list with Data Validation. Have demonstrated that in the attached workbook for the first two days in November.
Having said all that, I wonder why you chose this way to capture data. It's resembles noting down activities on a paper calendar, which is not the most effective way in Excel. Personally, I would create one table (can be for the whole year) with separate columns for Date, Provider, PPH, CT, PTs, No Show and Chart%. Then you can use built-in tools (like pivot tables and filters) to summarise the data in whatever format you need. Of course, you may have good reasons to set up the system as you did. Up to you.
Haetrox Have a look at the attached workbook. If I understood your intentions correctly, you may use SUMIF in column N on the Calculation sheet. No need for such a complicated AGGREGATE/INDEX/ MATCH function.
Couldn't replicate the #VALUE error you mentioned. See cell M20 on the Calculation sheet.
Lastly, you mention that you want drop-down lists for the names. You need to create a list with all possible names first and refer to that list with Data Validation. Have demonstrated that in the attached workbook for the first two days in November.
Having said all that, I wonder why you chose this way to capture data. It's resembles noting down activities on a paper calendar, which is not the most effective way in Excel. Personally, I would create one table (can be for the whole year) with separate columns for Date, Provider, PPH, CT, PTs, No Show and Chart%. Then you can use built-in tools (like pivot tables and filters) to summarise the data in whatever format you need. Of course, you may have good reasons to set up the system as you did. Up to you.