Count Number of Instances Column B >= Column A Without Helper Cells

As the description says I have two columns full of numbers. I want to count all the instances that the value in column B is >= its counterpart in column A. I have done this with helper cells but want to create an array based formula that does not require a helper column. I have attached an excel sheet that shows the output I should get using the helper column, and a formula I tried using to cut that column out. I left the equal sign out of G7 on purpose, because it just errors out as a function, and I wanted to show the path I tried to take to solve the problem.

First of all I converted the range into a table because tables are dynamic: they know how big they are and also you can define formulas in terms of column headings which make them easier to understand. In general Excel life goes a lot better if your data are in tables.

Then I use an obscure formula called SUMPRODUCT. SUMPRODUCT is semi retired since SUMIFS, COUNTIFS, AVERAGEIFS, etc. came along. However, there are still some applications, such as this one where it is still useful. SUMPRODUCT means sum of the products and it has a syntax of

=SUMPRODUCT ((Condition 1) * (Condition 2) * ... (Condition N) * C) Each of the conditions can be complex formulas that evaluate to TRUE or FALSE. TRUE is 1 and False is Zero. The C is an optional constant. Without the C it acts sort of like COUNTIFS. With the C it acts like SUMIFS where the C is the column you want to sum up. Also you can think of * as an AND condition and + as an OR condition and you can nest these within parenthesis. Suffice it to say, you can build some very complex evaluations with SUMPRODUCT.

In this case the magic formula In cell G3 is =SUMPRODUCT((Table1[B]>Table1[A]) *1) It means count up all the rows where B > A and multiply the count by 1.

You can play with this by adding rows to the table simply make an entry below the last row in the table and it becomes part of the table. Since the formula is built using the table, it will adjust to use exactly the right amount of rows.

That's a very interesting solution Pete, and yes it does exactly what I wanted. If you don't mind how does that work exactly? I cant figure out how putting 2 "-" converts that to a count function or gets to work without needing an array formatting.

Pete beat me to it. The -- means "evaluate this expression." It is the method most articles use.

I prefer to use * and + because it makes more sense to me. 1 * 1 is 1 and anything * 0 is 0.

Something like SUMPRODUCT ((A=1)*(B=2) * ((C=3) + (D=4))) is true if A AND B are true AND either (C OR D) are true.

I think the * + method gives more flexibility and for me, at least, is more intuitive.

Thank You dflak.

I like your use of mathematics to create Boolean logic. It makes it perfect sense in your example as you laid it out. This was a good learning experience to add tools to my belt.