Countifs formula

**Wirral Wizard** · 02-17-2010, 10:35 AM

I hope someone is able to help me with this problem...I've been banging my head all morning trying to find a solution!

I'll try to explain it as simply as possible!

Column A has a set of figures.

Column B has a set of figures and I want to count the number of times a cell in this column is greater than the figure in the corresponding cell in Column C. (e.g. count if B2 > C2).

But this should only be counted when the figure in Column A is less than a given criteria.

So, for example. When the figure in a cell in range A2:A20 <1.70 and the figure in the corresponding row in Column B is greater than the figure in the corresponding row in Column C it would count the result.

e.g.

A2 = 1.90 B2 = 2 C2 = 1 (Answer = 0 because A2 is 1.70 or higher)
A3 = 1.60 B3 = 1 C3 = 2 (Answer = 0 because C3 is greater than B3)
A4 = 1.50 B4 = 3 C4 = 0 (Answer = 1 because all criteria are met)

So how do I write a formula to get the total count of 1 in the example?

Countifs may be the solution but I'm struggling with the use of comparing a value in one range (i.e a column) with the value in another range.

Thanks for any help you can give!

**NBVC** · 02-17-2010, 10:40 AM

Try:

=COUNTIFS(A2:A4,"<1.7",B2:B4,">"&C2:C4)

adjust ranges to suit.

**Wirral Wizard** · 02-17-2010, 10:56 AM

Thanks NBVC. That's as far as I managed to get earlier and thought it had cracked it. But for some reason that formula is also counting where the cell in Column B is the same as or less than the corresponding cell in Column C.

**NBVC** · 02-17-2010, 11:00 AM

Are you sure... in my test it is doing as desired....

Are you ranges in those columns actually numbers and not text strings masked as numbers?

Try placing a 0 in any free cell, then copy it and select the 3 ranges in columns A to C. Then Paste Special and select Add...

Does that change anything?

If still problematic post a sample showing problem.

**Wirral Wizard** · 02-17-2010, 12:39 PM

This is getting stranger and stranger!

The '&' (SHIFT 7) seems to be causing a problem. In the original spreadsheet it is interpreting it as B2:B4 >0, and as all the values in Column B are greater than 0 it is counting every cell where column A is less than 1.70 and not making the calculation to see if the value of the cell in Column B is greater than the corresponding cell in Column C.

So then I opened a new spreadsheet to replicate the exact cell ranges you have quoted above with the same formula. And now I'm getting the answer 0 in the total! In the Countifs Function Arguments Box it has the following:-

Criteria_range 1 : A2:A4

Criteria 1: "<1.7"

Criteria_range 2 : B2:B4

Criteria 2: ">"&C2:C4

Outside Criteria 2 it now has the word '#Value!'

I'm confused!

**NBVC** · 02-17-2010, 12:43 PM

Possible to post at workbook showing the problem?

How does this formula respond?

=Sumproduct(--(A2:A4<1.7),--(B2:B4>C2:C4))

**Wirral Wizard** · 02-17-2010, 02:29 PM

Thank you so much NBVC, that has solved the problem. I don't quite understand how but it's done the job!

I really appreciate your help.

**NBVC** · 02-17-2010, 02:37 PM

I think I screwed up in test the Countifs() formula.. you are correct.. it doesn't work.. I don't use 2007 much (only by necessity) so assumed incorrectly and probably rushed it, I think.

See here for a great article on how the Sumproduct formula works...

**ginganababy** · 02-17-2010, 08:37 PM

try this array formula, you can put as many conditions as you like on this countif/sumif aslike formula

={sum((a1:a50<1.70)*(b1:b50<c1:c51)}

type the formula without the {}, but instead of hiting enter in the end, hit shift+Alt+Enter

Any doubts just ask