# Using COUNTIF in Excel to count number of "Greater than" values in an array of values

So here is my situation. I'm a principal creating a tracker for my teachers to use to track student growth on assessments. In the current tracker it is set up like this:

``````            % of            67%
Students Met

**Student Name    Week 1 Goal:         Week 1 Actual:        Goal Met?:**
Example A       23                    32                    Y
Example B       45                    44                    N
Example C       53                    55                    Y
``````

I use a formula for the "Goal Met?" column to put a Y or N depending on if the Actual meets or exceeds the Goal column. Then, I use another formula to determine the % of students Met by doing a COUNTIF of "Y"s in the Goal Met column, dividing it by a COUNTA of that column, and multiplying by 100.

What I'm wondering is... this is very cumbersome to use for teachers, and the more columns and formulas I put, the greater the risk of teachers typing in the wrong place and messing up the formulas... is there any way I can just do a COUNTIF of the Array of the Weekly Goal and Weekly Actual columns, and count only the rows where the Actual is >= the Goal? Like is there a way to do COUNTIF(E5:F17,F>=E) and put some sort of symbol as a variable by the E and F in the criteria so that it goes row by row and compares the values, then counts the ones where the F value is greater than the E value?

Any suggestions would be great because then I can eliminate the "Goal Met" column (which they invariably type in and I have to go back and fix weekly). It gets annoying have to protect every 3rd column on a massive tracker!

Thanks so much for any advice!

Brendan

• What about just hiding the column with the "Goal Met?" formula? Dec 31, 2014 at 18:25
• Why not write-protect the cells you don't want others editing? You could even use data validation to raise a pop-up message saying "Don't edit this cell" when someone clicks on the cell.
– user388043
Dec 31, 2014 at 19:13

You should always password protect your cells that you do not want changed.

To answer you question you will need to enter an array formula. Type the following in assuming your column names starts on A3 and then hit Ctrl+Shift+Enter.

``````=SUM(IF(C4:C6>=B4:B6,1,0))/COUNT(C4:C6)
``````

If you did it correctly curly brackets will appear around the whole formula.

``````={SUM(IF(C4:C6>=B4:B6,1,0))/COUNT(C4:C6)}
``````
• Interesting, Microsoft must have improved the behavior of `IF`. When I first was trying to do this, probably with Excel 2003 or so, `IF` didn't handle array inputs properly. Excellent! Dec 31, 2014 at 19:06
• @Brian - Excel 2003 could certainly do this (still can) - it needs "array entry", though, are you sure you were doing that? BTW you can use SUMPRODUCT and avoid the need for array entry e.g. just this "regular" formula `=SUMPRODUCT((C4:C6>B4:B6)+0)/COUNT(C4:C6)` Jan 1, 2015 at 12:10
• Wow. Seems my Excel-fu is not nearly to the level I thought. :-) I'm going to have to re-evaluate my bag of tricks, since many of them may not be relevant any more. As for `IF`, maybe I was using an even earlier version, Excel 2000 or 97, -- I clearly remember having a lot of trouble getting `IF` to work properly with array inputs. Jan 1, 2015 at 12:20
• There is still a fair amount of work if the list continues to grow updating the ranges to include the new data. To automatically increase the range take a look at dynamic named ranges using the OFFSET function. Enjoy. Jan 1, 2015 at 17:07
• @wbeard52 - dynamic named ranges is a good option +1, but there needn't be any work required if you don't use them - the above formulas can accommodate blank rows so there's no reason you can't use ranges that will be large enough to incorporate any expansion Jan 1, 2015 at 20:26

My Solution

The following array formula, with cell references adjusted appropriately, should do what you're looking for:

``````=SUM(1*((OFFSET(C2,0,0,COUNT(C:C),1)-OFFSET(B2,0,0,COUNT(C:C),1))>=0))/COUNT(C:C)
``````

Example: Set the C2 reference in the first `OFFSET` to the top cell of the 'Actual' data (F5 in your example), the B2 reference in the second `OFFSET` to the top cell of the 'Goal' data (E5 in yours), and set all of the C:C references to the column containing one or the other of the 'Actual' or 'Goal' data (E:E or F:F in yours). Be sure that the only cells with numerical data in the column you choose to replace the C:C references are those cells with 'Actual' or 'Goal' values in them, otherwise the formula will either return an inaccurate result, or will fail entirely.

To enter the formula as an array formula, type it in appropriately and then press Ctrl-Shift-Enter, instead of just Enter. (See here for more information on array formulas, or just search online for "excel array formula".) Any time you make a change to the formula, you will always have to enter it with Ctrl-Shift-Enter in order for it to behave properly.

How It Works

• Each `COUNT` retrieves the number of rows of data present.
• The two `OFFSET` calls retrieve the two arrays of interest, the 'Actual' and 'Goal' scores. In each call:
• The first argument marks the top-left cell of the array;
• The second and third arguments are the row and column offsets, respectively (both zero here);
• The fourth argument indicates the number of rows to include in the array; and
• The fifth argument indicates the number of columns to include.
• Since this is an array formula, subtracting the 'Actual' and 'Goal' arrays returns an array of the same size, but containing the element-wise difference between them.
• The `>=0` comparison converts the array of differences into an array of `TRUE`/`FALSE` values indicating whether or not the goal was met.
• The `1*` is there to convert the Boolean `TRUE` and `FALSE` values to numerical `1` and `0`, to then be counted up by the `SUM` function.
• In Excel, `TRUE` and `FALSE` implicitly carry the numerical values of one and zero.
• BUT, if you try to pass a range or array containing `TRUE`/`FALSE` values to a function like `SUM`, it will ignore `TRUE` values and return zero.
• SO, multiplying the whole `TRUE`/`FALSE` array by 1 is a way to trick Excel into interpreting the array in a useful way.
• The `SUM` divided by the `COUNT` is then the desired fraction of students meeting the goals set.

EDIT: Per wbeard52's answer, it should be possible in Excel 2010 or later to replace the `1*...` hack with an `IF` structure. In older versions of Excel, `IF` mishandled array inputs.

Another couple of array formula options, so you need to press Ctrl+Shift+Enter to commit them.

To find the number of goals met:

``````=SUM(--(C4:C6>B4:B6))
``````

To find the percentage of goals met:

``````=SUM(--(C4:C6>B4:B6))/count(C4:C6)
``````

You can use them independently. Remember to enter them as array formulas by pressing Ctrl+Shift+Enter.

Another "array formula" approach is like this:

`=AVERAGE(IF(E5:E17<>"",IF(F5:F17>=E5:E17,1,0)))`

confirmed with CTRL+SHIFT+ENTER

That gives you an actual % like 0.5 - if you want 50 multiply by 100