Use LARGE, IF, COUNT, and TEXT for Excel Exam Formulas
Question details
Calculate the five largest revenue amounts for a selected salesperson and count customer records for a selected month.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A user is working with sales data and needs conditional formulas for an exam or worksheet.
- Observed behavior
- The worksheet should return matching revenue amounts in descending order and a count of customer records for the chosen month.
For the examples, place salesperson names in A2:A100, revenue in B2:B100, sales dates in C2:C100, and customer names in D2:D100. Enter a salesperson in H1 and a real Excel date in the desired month in H2; adjust the ranges to match your worksheet.
Calculate the top five revenue amounts for a salesperson
Use LARGE and IF to rank revenue values belonging to the salesperson selected in H1.
In I2, enter =IFERROR(LARGE(IF($A$2:$A$100=$H$1,$B$2:$B$100),ROWS($I$2:I2)),""). In older Excel versions, confirm it with Ctrl+Shift+Enter instead of Enter.
Drag the fill handle from I2 through I6 to return the first through fifth largest matching revenue amounts.

Count customer records for a selected month
Use COUNT, IF, and TEXT to count rows with a customer name and a sales date in the month and year selected in H2.
Calculate revenue rankings and monthly counts in WPS Spreadsheets
Open your sales workbook in WPS Spreadsheets and use the same conditional array formulas to rank revenue and count monthly customer records.
- 1. Open the workbook: In WPS Spreadsheets, choose Open and select your Excel workbook.
- 2. Set up the criteria: Enter the salesperson in H1 and a valid date in the target month in H2. Arrange the data in columns A through D as described above, or adjust the formula ranges.
- 3. Rank matching revenue: Enter the LARGE and IF formula from the first solution in I2, then drag the fill handle down through I6.
- 4. Count monthly records: Enter the COUNT, IF, and TEXT formula from the second solution in an empty cell. If your WPS version requires array entry, confirm each formula with Ctrl+Shift+Enter.

Frequently Asked Questions
Why does the revenue formula return blank cells?
IFERROR displays a blank when LARGE cannot find another numeric matching revenue amount. Check that H1 matches a salesperson in column A and that revenue values in column B are numeric.
Do I need Ctrl+Shift+Enter for these formulas?
Current Excel versions can evaluate the array calculations with Enter. Older versions may require Ctrl+Shift+Enter when entering or editing each formula.
Does the monthly count include the same customer more than once?
Yes. It counts each qualifying row with a nonblank customer name and a numeric date in the selected month and year; it does not count distinct customers.
Why is a date missing from the monthly count?
Check that the sales date in column C and the selected date in H2 are real spreadsheet dates rather than text. The formula also excludes rows with a blank customer name in column D.




