Search code examples
excelexcel-formulacountif

Use native Excel functions to count occurrences over threshold

I am trying to work out how to do the following. I have two Excel sheets: one for data and one for stats. The data sheet stores the number of units bought by each named customer - on multiple occasions. The stats sheet has a single row for each customer name - but should show in a separate column how many times the units bought exceeded a certain threshold.

For example:

Data Sheet

And:

Stats sheet

I am adept at VBA, and could produce a custom function to do this. However, I want to find a way to do this within non-VBA formulæ: i.e standard Excel nested formulas. This is because it is for a corporate spreadsheet, where the management wants to keep things as simple as possible - in xlsx format.

I'm sure it could involve a COUNTIFS function - but can’t quite get my head around which combination of native functions to use. Any assistance gratefully received.

Solution

  • Assuming that the name of the sheet where the data are is Sheet1, place this formula in the stats sheet's cell B2 and copy down till the end of the names, will return the required result:

    =COUNTIFS(Sheet1!B$2:B$10,">5",Sheet1!A$2:A$10,A2)

    actual range sizes and limit are according to the screenshots.