Pulling cell data based on date and name

I do something very similar. I use a metrics sheet to pull the data from a larger sheet, then reference that metrics sheet for the dashboard display. The example below uses SUMIFS, but you could use AVG to obtain the average status if your status is a percentage or numeric value.

In my example, I want to show the total value of all Bills of Lading in Closed status for each warehouse employee. In my metrics sheet I have a Name column and a Data column. Obviously the employee name goes into the Name column, and in the data column I use SUMIFS and reference the grid sheet (called "BOL Active Tracking") as follows:

=SUMIFS({BOL Active Tracking Value}, {BOL Active Tracking Status}, "Closed", {BOL Active Tracking Assigned To}, Name@row)

In plain English: Add up the values in rows in "BOL Active Tracking" sheet with Closed in the Status column, where the Assigned To person matches the Name column for this row.

When writing your formula, use the prompt to "Reference another sheet" to select the appropriate column range in the project sheet. The system will allow you to select the sheet, the column, and to name the range something meaningful to you. In my case I just use the Sheet name followed by the Column name, ex {BOL Active Tracking Value}. The system adds the {} brackets around the reference range.

Result:

Name Data

JOHN SMITH $189,560.00

JANE DOE $157,495.08

JACK SPRAT $24,128.00

etc.

Fantastic! 4 years later. (This is why I always try to explain my answers in detail!)