Function Problems

Use LARGE, IF, COUNT, and TEXT for Excel Exam Formulas

Maira MehtabMaira Mehtab Nov 18, 2026 869 views

Question details

Calculate the five largest revenue amounts for a selected salesperson and count customer records for a selected month.

Use LARGE, IF, COUNT, and TEXT for Excel Exam Formulas
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.
Before you start

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.

Solution 1Recommended

Calculate the top five revenue amounts for a salesperson

Use LARGE and IF to rank revenue values belonging to the salesperson selected in H1.

1
Enter the ranking formula

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.

2
Fill down

Drag the fill handle from I2 through I6 to return the first through fifth largest matching revenue amounts.

Calculate the top five revenue amounts for a salesperson
Fewer than five matches: IFERROR leaves remaining cells blank if there are fewer than five numeric revenue amounts. Tied amounts can appear more than once.
Work with sales formulas in WPS

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. 1. Open the workbook: In WPS Spreadsheets, choose Open and select your Excel workbook.
  2. 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. 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. 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.
Rank revenue for a selected salesperson.Count customer records by month and year.Open and save Microsoft Excel XLSX workbooks.Use a familiar spreadsheet interface.
QA img-9

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.