Formula Error

Question

Trying to create an excel sheets that shows top 3 attendees for each month of the year. My formula for searching and calculating to find the information is showing up as incorrect and i'm confused as to why it is

Answer 1

@ Aaron,

(see the screenshot above)

Apply in cell E1 below formula:

=LET(mth, A2:A21, att, B2:B21, pt, C2:C21, t, LET(wr, WRAPROWS(TEXTSPLIT(ARRAYTOTEXT(BYROW(UNIQUE(mth), LAMBDA(a, LET(sr, SORT(FILTER(HSTACK(mth, att, pt), IFERROR(SEQUENCE(ROWS(mth)) / (mth = a), FALSE)), 2, -1), ARRAYTOTEXT(FILTER(sr, BYROW(TAKE(sr,, -1) = LARGE(TAKE(sr,, -1), {1,2,3}), OR))))))),, ", "), 3), IF(ISNUMBER(--wr), --wr, wr)), chc, CHOOSECOLS(t, 1), VSTACK(HSTACK("Name", TOROW(TEXT(UNIQUE(MONTH(chc & 1)) * 28, "mmm"))), DROP(PIVOTBY(CHOOSECOLS(t, 2), MONTH(chc & 1), CHOOSECOLS(t, 3), SINGLE,, 0,, 0), 1)))

or apply in cell K1 below formula:

=LET(mth, A2:A21, umth, MONTH(mth & 1), unq, UNIQUE(mth), VSTACK(HSTACK("Month", "3 best Attendance"), HSTACK(unq, BYROW(BYROW(SEQUENCE(ROWS(unq)), LAMBDA(a, ARRAYTOTEXT(CHOOSECOLS(SORT(VSTACK(TOROW(CHOOSEROWS(TEXTSPLIT(TEXTJOIN(";",, DROP(GROUPBY(umth, B2:B21, ARRAYTOTEXT,, 0),, 1)), ", ", ";"), a), 3), TOROW(CHOOSEROWS(--TEXTSPLIT(TEXTJOIN(";",, DROP(GROUPBY(umth, C2:C21, ARRAYTOTEXT,, 0),, 1)), ", ", ";"), a), 3)), 2, -1, TRUE), {1,2,3})))), LAMBDA(b, TEXTBEFORE(b, ",", 3))))))

Let me know if these formulas worked for you or not. If they work then please mark my reply as a Valid Answer and kindly upvote it.

IlirU

Answer 2

Hi @Aaron,

Thank you for your prompt update.

Based on your sharing, the issue in your formula is the @'Tour Attendance Tracker' part. The @ sign is only valid for structured references inside Excel Tables and it doesn't work for normal worksheet range. Therefore, Excel sees your formula having invalid syntax.

You can try using this formula instead:

=INDEX('Tour Attendance Tracker'!$C$2:$C$500,MATCH(1,('Tour Attendance Tracker'!$A$2:$A$500=$A2)*('Tour Attendance Tracker'!$F$2:$F$500=$C2),0))

I also added some $ sign to lock the range (C2:C500, A2:A500 and F2:F500) and lock your lookup cells if you plan to copy down the formula. If it's not what you prefer, you can just remove the $ sign and it still works properly.

Hope this will help. If you have any questions or need further clarification, please feel free to reach out again and I’ll be happy to continue assisting you.

Thank you for your patience and understanding, and I look forward to supporting you further.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

To troubleshoot the formula error you are experiencing in Excel while trying to find the top 3 attendees for each month, consider the following common issues:

1. Formula Structure: Ensure that your formula is correctly structured. If you are using functions like LARGE or RANK, verify that the ranges you are referencing are correct and that they contain the expected data.
2. Data Type: Check that the data types of the values you are working with are consistent. For example, if you are comparing numbers, make sure they are not formatted as text.
3. Closed Workbooks: If your formula references cells from a closed workbook, it may return errors. Make sure that all referenced workbooks are open, or consider using a different approach to access the data.
4. List Separator: If you are using a character to separate arguments in your formula, ensure that it matches the list separator defined in your Windows Regional settings. Mismatched separators can lead to formula errors.
5. Array Formulas: If you are using array formulas (like SUM(IF(...))), remember to enter them using CTRL+SHIFT+ENTER to ensure they are recognized as array formulas.

If you continue to face issues, providing the specific formula you are using could help in diagnosing the problem further.

---

References:

• Formula errors in Excel when list separator isn't set correctly
• SUMIF, COUNTIF, and COUNTBLANK functions return "#VALUE!" Error