Average if across sheets

[andgoal]

To the Bionic Excel Guru in the House,


Case Scenario: On each day Monday-Saturday, each car dealer employee turns in a daily sales report on each car sale they had on that given day.


For example: AgentX had 3 sales on Monday, 5 sales on Tuesday, 0 sales on Wednesday, etc. If the employee turns in the daily report for that day his name will be chosen in a drop down list in sheets 1-5 respectively (Column A) then the dollar amount for each sale (Column D). Agent X can have many or zero rows in each sheet.


In a nut shell: Sum the dollar amount for each car sale by employee by day then calculate the average dollar amount in a summary cell.




I have a drop down list of employee names on each sheet (Column A).


Sheet 1 = Monday
Sheet 2 = Tuesday
Sheet 3 = Wednesday
Sheet 4 = Thursday
Sheet 5 = Friday
Sheet 5 = Saturday
Sheet 6 = Admin (Summary page where I want the formula to be calculated for each employee. The employee names are static in Column A).


Sheet names (mon-sat) are listed in the 'Admin' page B2:B7


I was able to cook up a formula, but it's only averaging the data input for Monday, and not the other Days. Please help!




Here is what I have:
=IFERROR(AVERAGEIF(INDIRECT("'"&Admin!$B$2:$B$7&"'!A2:A150"),"Agent X",INDIRECT("'"&Admin!$B$2:$B$7&"'!D2:D150")),0)








Any feedback will be greatly appreciated!


Regards

 

[XOR LX]

Hi,

=SUMPRODUCT(SUMIF(INDIRECT("'"&Admin!$B$2:$B$7&"'!A2:A150"),"Agent X",INDIRECT("'"&Admin!$B$2:$B$7&"'!D2:D150")))/SUMPRODUCT(COUNTIF(INDIRECT("'"&Admin!$B$2:$B$7&"'!A2:A150"),"Agent X"))

Regards

 

[andgoal]

You are a life saver XOR LX! Worked like a charm!

Side note: Any idea how can I copy down the formula and modify the agent name in each cell. Currently if I update the formula in one cell, it updates them all to the same agent?

 

[andgoal]

You are a life saver XOR LX! Worked like a charm!

Side note: Any idea how can I copy down the formula and modify the agent name in each cell. Currently if I update the formula in one cell, it updates them all to the same agent?

 

[andgoal]

Nvm, I got it. I just clicked on the little thunder bolt icon on the cell and selected to stop auto calculating all cells.

 

[XOR LX]

Ok, glad you got it to work.

And you're welcome!

Cheers