We send inspectors to stores once or maximum twice a month and we have a store scoring system.
Audit information is written on the home page, including the date on which the auditor went and where the auditor went.
Additionally, there is a page called scores, and on the score page, I can sort the scores and inspector names by store, using VLOOKUP, and the names of the inspectors by month in the column header.
HOMEPAGE
| DATE | STORE NAME | PERSON | AUDITPOINT | |
|---|---|---|---|---|
| 01.01.2024 | NEW YORK | JOHN DOE | [email protected] | 100 |
| 11.01.2024 | NEW YORK | JOHN DOE | [email protected] | 80 |
| 03.01.2024 | UTAH | JACK WILSON | [email protected] | 90 |
| 04.01.2024 | NEVADA | JACK WILSON | [email protected] | 90 |
| 12.01.2024 | NEVADA | JOHN DOE | [email protected] | 75 |
| 01.02.2025 | NEVADA | JOHN DOE | [email protected] | 75 |
The page where the formula should be.
POINT
Search according to the current month of 01/01/2024, that is, audits between 01/01/2024` and 31/01/2024 D2 IS 01/01/2024
| STORE | 1. POINT | 1. PERSON | 2. POINT | 2. PERSON | AVERAGE |
|---|---|---|---|---|---|
| NEW YORK | 100 | JOHN DOE | 80 | JOHN DOE | 90 |
| UTAH | 90 | JACK WILSON | 90 | ||
| NEVADA | 90 | JACK WILSON | 75 | JOHN DOE | 82.5 |
Google Sheet Link : google sheet table
2
You may try:
=torow(sortn(filter({HOMEPAGE!$F:$F,HOMEPAGE!$D:$D},HOMEPAGE!$C:$C=$B10,eomonth(HOMEPAGE!$B:$B,)=eomonth(D$2,)),2,,1,))
3