Return Average Dates Matching Certain Criteria Across Multiple Sheets

grnaskd

New Member
Joined
Dec 2, 2014
Messages
9
Hello

I'm having trouble getting a formula to return the average date instead of its sum.

=SUMPRODUCT(SUMIFS(INDIRECT("'"&F2:F58&"'!C3:C338"),INDIRECT("'"&F2:F58&"'!I3:I338"),"*"&A2&"*"))

I have 57 sheets which I'm using the INDIRECT formula to capture each sheet. After that I'm selecting my dates column (C) then filteringf for my criteria (I). Lastly my Criteria is (A2).

I know the formula is capturing the correct dates matching with the criteria I selected because when I manually select them to view the average in the bar next to the zoom, I see the average and the sum, with the sum matching what I get.
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Hello

I'm having trouble getting a formula to return the average date instead of its sum.

=SUMPRODUCT(SUMIFS(INDIRECT("'"&F2:F58&"'!C3:C338"),INDIRECT("'"&F2:F58&"'!I3:I338"),"*"&A2&"*"))

I have 57 sheets which I'm using the INDIRECT formula to capture each sheet. After that I'm selecting my dates column (C) then filteringf for my criteria (I). Lastly my Criteria is (A2).

I know the formula is capturing the correct dates matching with the criteria I selected because when I manually select them to view the average in the bar next to the zoom, I see the average and the sum, with the sum matching what I get.

Try...
Rich (BB code):
=SUMPRODUCT(
  SUMIFS(INDIRECT("'"&F2:F58&"'!C3:C338"),INDIRECT("'"&F2:F58&"'!I3:I338"),"*"&A2&"*"))/
 SUMPRODUCT(COUNTIFS(INDIRECT("'"&F2:F58&"'!I3:I338"),"*"&A2&"*"))
 
Upvote 0
Let me know if this helps:

-In Column A I have a list of names I will use as the referenced criteria
-In Column B next to the criteria will be the formula to find the average dates across the multiple sheets.
-The dates are in each sheet between C3:C338
-After referencing the criteria from column A on a separate sheet, the formula will pull from each sheet the corresponding criteria between I3:I338

Sheet1
Column A| Column B
BRAGG | (Average Date of Departure)
Granini | (Average Date of Departure)
Element | (Average Date of Departure)

Sheets 2-57
List rooms each company occupies (I3:I338) and their personnel's departure date. I need to pull from each sheet for example, average date (C3:C338) all "AC First" personnel will depart.

Thank you.
 
Upvote 0
Thank you for your help. I figured out that I needed to divide by the number of entries there were which gave me for example:

=SUMPRODUCT(SUMIFS(INDIRECT("'"&G2:G58&"'!C3:C338"),INDIRECT("'"&G2:G58&"'!I3:I338"),"*"&A22&"*"))/3

I cant give too many details as this spreadsheet is for the military and troop movement but each room has three bed spaces (three occupants per room), while one room may have three people in it others may have one or two. The example above is for a smaller units that has two rooms (six bed spaces) but only has three people occupying them (one in one room and two in the other). I found that the formula was returning the sum of all the dates (three entries) and when I divided by three I got the correct average.

This leads me into my next issue which I'll post in another thread. Hope you can help there also.
 
Upvote 0
Why 3?

If the divisor is always 3, keep it as is. If not:

=SUMPRODUCT(SUMIFS(INDIRECT("'"&G2:G58&"'!C3:C338"),INDIRECT("'"&G2:G58&"'!I3:I338"),"*"&A22&"*"))/
SUMPRODUCT(COUNTIFS(INDIRECT("'"&G2:G58&"'!I3:I338"),"*"&A22&"*"))

which should give an average, provided that I3:I338 is text-based.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top