Count if Value in Range 1 greater than value in Range 2

**solnajeff** · 11-24-2009, 03:16 PM

Hi

I have two ranges that show parts of a question to be answered and those parts already answered (aa6:aa55 and ab6:ab55). I would like to count those elements where the value in column AA is greater than the corresponding value in column AB, showing questions with parts still to be answered. Currently I have a formula comparing the two and am counting the occurrences of true or false. This seems to be overkill and I am sure that there is an array formula that can do the same thing more efficiently, unfortunately I cannot figure it out.

Thanks for any help you can offer.

Regards

Jeff

**NBVC** · 11-24-2009, 03:24 PM

Do you mean?

=SUMPRODUCT(--(AA1:AA100>AB1:AB100))

adjust ranges to suit.

**solnajeff** · 11-24-2009, 07:46 PM

Hi

Thanks for the help. I had tried using sumproduct, but did not have the double dash and it did not work. Could you please tell me what is the significance of this part?

Thanks again

Jeff

**DonkeyOte** · 11-24-2009, 07:48 PM

It, the double dash, is the double unary and is used to coerce Boolean output (True/False) to integer equiv. (1/0 respectively) - in this instance these Integer values are then simply summed.

For more detailed info re: SUMPRODUCT and coercion see the link in my sig. to Bob Phillips' white paper.

**teylyn** · 11-24-2009, 07:52 PM

Jeff,

this is called the double unary operator. It is used to turn boolean TRUE and FALSE into numeric 1 and 0 respectively, so the result can be used for a calculation.

Watch the formula in the Evaluate Formula tool and see how it transforms

--({TRUE,TRUE,FALSE,TRUE,FALSE,FALSE})

into

({1,1,0,1,0,0})

hth