Working Averages over multiple criteria

[Semaj0]

Hi all,

I have a spread sheet where I record different departments response times to requests for information.

I am looking for a formulae which will return an average (mean) for each department, so far so simple just simply using this formulae =AVERAGEIF(F$2:F$700,"Corporate",L$2:L$700) which returns a value on everything within that search criteria.

However what I need is a formulae which will return a value when the work has been completed (on time or not) AND what has exceeded 20 days still on going but not before.

The columns I use for this are . . .

"Disclosed within 20 days" which returns a "Yes" or "No" depending on completion time and "N/A" which shows that the work is still outstanding.

"Total Days" which is a rolling count of each individual piece of work.

Thanks for any help this forum can provide, doing this manually is fairly time consuming.

 

[Joe4]

Welcome to the Board!

Average is just the sum of all the values meeting all your criteria divided by the count of all the values meeting your criteria.
You can get the sum of all values meeting multiple criteria using the SUMIFS function (see: https://www.techonthenet.com/excel/formulas/sumifs.php)
And you can get the count of all values meeting multiple criteria using the COUNTIFS function (see: https://www.techonthenet.com/excel/formulas/countifs.php)

So, you average would just be the result of the SUMIFS formula divided by the result of the COUNTIFS function.

Using the links provided, see if you can get this to work. If you run into trouble, post back the formulas you came up with.

 

[Semaj0]

Hi Joe

Thanks for this, been thinking about this the wrong way really, solution is simple and effective.

Thanks again.

 

[Joe4]

You are welcome!