Aggregate Index AVERAGE/SUM

[Haetrox]

Hello,

I am attempting to make a template/calendar where it lists certain information then I can Index/MATCH multiple ranges to match a name such as "BOB" and pull the next cell overs data as a list to later on average/sum or pull the information and average it all in one cell. 

I was utilizing this as to make my formula as it seemed like it could work but it's stopping at the first value and I cannot figure out why: https://www.xelplus.com/return-multiple-match-values-in-excel/ 

L6 I am using as my "helper" cell and L16 is the newest formula.

L16 Formula:

=IF(ROWS($L$7:L7)<=$L$6,INDEX($B$4:$B$106,AGGREGATE(15,3,(($A$4:$A$106=$M$6)/($A$4:$A$106=$M$6)*ROW($A$4:$A$106))-ROW($A$3),ROWS($L$7:L7))),"")

Also: When attempting to reference the data from "November" sheet to "calculations" to list maybe only the names and other cells such as A4:F8 with formula =November!A4:F8 it shows #VALUE! even on the same sheet. 

GOAL: Have a calendar like template with drop down menu for changing names, fill in information as each day changes, fill in green cells with 1 single formula each to search for a match and then calculate either as SUM or AVERAGE. 

[#2 · Accepted solution]

@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.

[#3]

@Riny_van_Eekelen 

Thank you so much! Your SUMIF and the use of AVERAGEIF helps this so much! Also thank you for the DataValidation, I guess I missed that portion in that video and excel how to. 

What would you suggest in regards to referencing sheets such as NOVEMBER/DECEMBER and making a list such as that of the sheet CALCULATIONS with this data as the Arrays don't move if I cut and move this around for new months? 

Attached is the newest file as I cleaned up using the information you provided.  

[#4]

@Haetrox Glad you did get SUMIF and AVERAGEIF to work and sorted out the data validation bit. With respect to referencing the monthly sheets, I'd get rid of them all together.

As mentioned in my previous post, I don't really understand why you use these monthly sheets as the are difficult to maintain and summarise. Isn't that exactly what you are experiencing? My suggestion is to go for a sequential list of "events" as described in my previous post and use built-in Excel functionality to summarise the data. Obviously, that means a complete redesign of your schedules.

By the way, did you notice that your NOVEMBER and DECEMBER sheets have two ranges for day 17? I suspect that it is not intentional. And, be aware that using merged cells is often causing trouble copying, cutting, pasting, filtering, sorting and referencing cells/ranges. Better to avoid the use of them.