Formula needed to compare cells in two columns

[jlwilliams]

Hello... I have a table where I need to count the number of times the cell in Column B is >= to the corresponding cell in Column A. So looking at the below example, there are two situations where Column B ("Total Enrollment")=Column A ("Capacity"), so I need a formula that is going to give me a product of "2" in this situation. I hope this makes sense!
Capture.PNG

[vlady]

Hello
try sumproduct

=SUMPRODUCT((A1:A20=B1:B20)*(A1:A20<>""))

[MarvinP]

Hi,

Why not do it with a simply if statement and a helper column? See the attached.

[jlwilliams]

vlady ~ Thank you!! That works

MarvinP ~ The table is a query being fed into my workbook and I've been having problems with Excel removing the query every time I add helper columns, so I'm trying something new. But thanks anyway!

[jlwilliams]

Hi vlady,

Actually this did not work. I need it to include the instances when the number in Column A is bigger than the number in Column B, as well as when it's equal. How do I do that?

Hello
try sumproduct

=SUMPRODUCT((A1:A20=B1:B20)*(A1:A20<>""))

[Cutter]

Try adding the > symbol

=SUMPRODUCT((A1:A20>=B1:B20)*(A1:A20<>""))

[jlwilliams]

That does not give me what I'm looking for, but thanks anyway.

Try adding the > symbol

=SUMPRODUCT((A1:A20>=B1:B20)*(A1:A20<>""))

[Cutter]

What is it that you're looking for - because firstly you said this:

I need to count the number of times the cell in Column B is >= to the corresponding cell in Column A

but then you said this:

I need it to include the instances when the number in Column A is bigger than the number in Column B

[jlwilliams]

Oh gosh, I'm sorry. I'm confusing even myself! So I need to know if (for example) A2-B2<=0. Or in other words, as I said the first time, if B2 is greater than or equal to A2. And then I need to count the number of times this is the case in the entire table.

This is a query table that gets refreshed weekly, so I need it to refresh the calculation each time as well.

What is it that you're looking for - because firstly you said this:


but then you said this:

[Cutter]

If you're not concerned with checking for blank cells then you just need this:

=SUMPRODUCT(--(B2:B100>=A2:A100))

[jlwilliams]

YES! YES, YES, YES!!! Thank you so much, Cutter!

If you're not concerned with checking for blank cells then you just need this:

=SUMPRODUCT(--(B2:B100>=A2:A100))

[Cutter]

You seem very happy. You're welcome.