Re: Counting

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Hi Adam

Sumproduct is being used to calculate the outcome of 2 arrays.
There are a series of tests which will return True or False, which upon
multiplication will be coerced to 1 or 0
=SUMPRODUCT((A1:A50>=1)*(A1:A50<=2))
(A1:A50>=1) will return either True or False
(A1:A50<=2) will return either True or False

Multiplying them together will only give a result of 1, where both cases
are True (1 * 1), and therefore both conditions are met.
Sumproduct then adds all these results to give your answer.

For more information on Sumproduct (and alternative ways to coerce True
and False to 1 and 0), take a look at Bob Phillips excellent treatise at
http://xldynamic.com/source/xld.SUMPRODUCT.html

--
Regards

Roger Govier


"AdamMCW" <AdamMCW@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:9DEBB2C5-9B38-4BD2-AED0-3D60C4DDE8E7@xxxxxxxxxxxxxxxx
Thanks to you both.

I see how Biff's works with subtraction.

Although yours looks more direct Ragdyer I'm not clear why it works.
I'm
assuming the "*" is not intended to multiply anything and is instead
redifined when used with the Sumproduct function. Is this correct?

"AdamMCW" wrote:

I have a list of scores 1.1, 3.2, 1.2, 2.4, 1.1, 3, etc... . I need
to know
how many are between 1 and 2. Is this a countif? I can't seem to
figure out
how to use two conditions.


.

Quantcast