Average, Median, multiple criteria, unique values, dynamic worksheet

**branchoff** · 12-12-2016, 05:10 PM

Hello!
Re this thread http://www.excelforum.com/showthread.php?t=1165124
I too, am trying to do something similar and all the coding I know is trial and error. I am struggling terribly with these array formulas.
I have a Data Entry worksheet from which I am trying to get monthly data into an Incident worksheet. The Data Entry worksheet will be dynamic and will be queried by year, by month, by location, by incident type. On the Data Entry worksheet,

Column D has the record number (person identifier; same record number may show up multiple times)
Column F has the incident date
Column K has the Incident type
Column AB has the duration of the incident
Column AC has the month
Column AE has the year
Column AF has the location
Column AQ is a concatenation of D and F. Unique records are those where the combination is unique

In the Incident worksheet I need to put formulas that will pull:
1) Total incidents (Data Entry Sheet Column $AQ) for the particular year (Incident Report $B$1), for a particular month (Incident Report $A$3), for a particular location (Incident Report $B$3) by type of Incident (Escalated; Non Escalated found in Data Entry Sheet Column K).

2) Average duration of incidents with same qualifiers as above

3) Median Duration of incidents with same qualifiers as above

My formulas are as follows:
=COUNTIFS('Data Entry'!$K:$K,"Non Escalated",'Data Entry'!$AC:$AC,Incident Report!$A$3,'Data Entry'!$AE:$AE,Incident Report!$B$1,'Data Entry'!$AF:$AF,Incident Report!$B$3)

=AVERAGEIFS('Data Entry'!$AB:$AB, 'Data Entry'!$AC:$AC,Incident Report!$A$3, 'Data Entry'!$AF:$AF,Incident Report!$B$3,'Data Entry'!$K:$K,"Non Escalated",'Data Entry'!$AE:$AE,Incident Report!$B$1)

=MEDIAN(IF('Data Entry'!$AF:$AF = Incident Report!$B$3,IF('Data Entry'!$AC:$AC = Incident Report!$A$3,IF('Data Entry'!$K:$K ="Non Escalated",IF('Data Entry'!$AE:$AE = Incident Report!$B$1, 'Data Entry'!$AB:$AB)))))

I need to do the same formulas, but this time only for the unique values in Data Entry AQ. Also, any help with my existing formulas will be appreciated-the code takes a few moments to execute even though I have the following at the beginning of my code:

Application.ScreenUpdating = False
    Application.Calculation = xlManual
    Application.EnableEvents = False

This is my first post, and I read the rules, but please educate me about anything that needs to be changed in my post/approach.

**protonLeah** · 12-12-2016, 07:31 PM

(How to) Upload a Workbook directly to the Forum
(please do not post pictures or links to worksheets)

Click Advanced next to Quick Post button at the bottom right of the editor box.
Scroll down until you see "Manage Attachments",
Click the "Choose" button at the upper left (upload from your computer).
Select your file, click "open", click "upload"
Once the upload is completed the file name will appear below the input boxes in this window.
Close the Attachment Manager window.
Click "Submit Reply"

Note: Please do not attach password protected workbooks/worksheets
Ensure to disable any Workbook Open/Autorun macros before attaching!

**branchoff** · 12-12-2016, 09:38 PM

Hello!

I did not post a link to my worksheet. I posted a link to a thread in the forum that had a similar question to mine. I seem to recall that's what I read in the rules...

Is it that you are suggesting that I upload my workbook?

**branchoff** · 12-14-2016, 12:04 PM

I am attaching the file. Please keep in mind that I inherited this. The data entry is set up to mirror a paper form, and I have no control over how it is set up. The highlighted fields on the incident sheet are the formulas I need help with. Thank you so much!