Use AVERAGEIF Across All files in a folder

[eliguillot]

I have a file for every workday over the past few months and want to average some of the values that are in the relative same location in each workbook.

I currently have a master workbook that has a list of values that I am using for the "criteria" in the AVERAGEIF formula.

For each item in the A column of my master workbook I want to average all of the instances of it across every file in the directory.

Basically I want to have "=AVERAGEIF(all B columns in the worksheet named "PRODUCTION" in each workbook, the text criteria in the cell next to this one in my master workbook, values in column F of the same row as the subject criteria I'm averaging)

Please let me know if anymore clarification is needed.

I've been doing some research and it seems that a macro would be the best bet. I have never written a macro, but am relatively familiar with coding logic.

I don't know how to write the formula into the macro.

 

[mrshl9898]

There doesn't seem to be anything certain to go off here, but an average is sum divided by a count. Maybe do something along the lines of a SUMIF divided by a COUNTIF?

That, or perhaps upload an example of what you are working with and what you are trying to do exactly.

 

[eliguillot]

I want to average all the instances across all the workbooks.

For instance, each workbook might have 2-5 of the different criteria that I'm looking for with different values in each workbook. I want to average all of the values across all the workbooks.

The data I am looking to average is production rate.

Different production lines have different rates.

The criteria that I'm using is the name of the production line (route) and the values I want to average are the number of cases/hour.

One workbook might have the route (6/10 Labeling) with a value of 150 and another may have it with 134 and another with 128. I want to be able to average those.

 

[eliguillot]

I'm able to use the "AVERAGEIF" successfully, but can only specify one external workbook at a time.

 

[mrshl9898]

So AVERAGE(AVERAGEIF,AVERAGEIF,AVERAGEIF)?

Or SUMIF+SUMIF+SUMIF/COUNTIF+COUNTIF+COUNTIF.

 

[eliguillot]

right, but then I have to do it manually for each workbook. I'm experimenting now with trying to use cells with the date in the same format as the filenames so that as I drag the formula across it will rename the filename that it should look in.

This would only return single values for each day, but that would be okay and possibly even better as I could average them later and then also run other statistic stuff like range, which would also be useful.

I'm unable to use a cell reference as part of the filename in my formula though.

 

[mrshl9898]

Ok, have you tried using INDIRECT?

B1: =[Sheets.xlsx]Sheet1!$E:$E

is the same as

A1: [Sheets.xlsx]
B1: =INDIRECT(A2&"Sheet1!$E:$E")

*Just note if you have a space in the book name you'll need to include the ' before the name. Since this doesn't get picked up in the INDIRECT you'll need to use "'"& in front of the string for the reference.

INDIRECT also requires the workbook to be open.