Excel count and countif functions are not working in an array

Question

When looking for the cell count in an array of numbers for an average, above average or below average, Excel only returns the total number in the array as opposed to the actual number of above, below or within the average.

Answer 1

You have to use COUNTIF.

And you should place the cell reference B60 outside the quotes, otherwise the formula looks for the literal text "B60".

=COUNTIF(B2:B31, ">"&B60)

Answer 2

Hi MONTGOMERY, JONATHAN

Good day! Welcome to Q&A forum. 

When using COUNT or COUNTIF to evaluate how many values in a range are above average, below average or equal to average, Excel may return the total number of cells in the array rather than the number of cells meeting the specific condition. This typically happens when the formula isn't structured correctly to compare each cell against the average. 

To count cells above average, use an array formula or a combination of functions like this: =COUNTIF(A1:A10, ">" & AVERAGE(A1:A10)) 

To count cells below average: =COUNTIF(A1:A10, "<" & AVERAGE(A1:A10)) 

To count cells equal to average: =COUNTIF(A1:A10, "=" & AVERAGE(A1:A10)) 

Common Pitfalls:

• Data type mismatch: Kindly ensure all cells in the range are numeric. If some are text, COUNTIF may behave unexpectedly. 
• Array formulas: If you're using older versions of Excel, you might need to enter array formulas using Ctrl + Shift + Enter. 

 

Please let me know if there are any updates or further concerns. I am happy to help. 

---

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

In Mirosoft Office 365, I found another pitfall.

=COUNTIF(A1:A10, ">" & CELL@##) try to decrease decimal for value or formula result.