software

Hi All,

I am sure this is just a simple question for the gurus here.

I have recently come accross a spreadsheet with the following formula:

sum((Column Agt;=X)*(Column Alt;Y))

The result was a count of everything in column A that has the value greater
or equal to X while less than Y. It's an interesting approach, but I am
wondering how the operator quot;*quot; is being used? In addition, why use quot;Sumquot;,
and not quot;Countifquot;?

Thanks,

Hi!

That formula is multiplying 2 arrays together. The results of the
multiplication will return an array of 1's and 0's which are then added
together in the Sum function.

Sort of like this:

A1gt;=X = TRUE * A1lt;Y = FALSE
A2gt;=X = TRUE * A2lt;Y = TRUE

TRUE * FALSE = 0
TRUE * TRUE = 1

SUM(0,1) = 1

The same formula written with Countif:

=COUNTIF(A1:A10,quot;gt;=Xquot;)-COUNTIF(A1:A10,quot;gt;Yquot;)

=SUM((A1:A10gt;=X)*(A1:A10lt;=Y))

As you can see, one formula is shorter. So, that's probably why it's being
used.

Biff

quot;JamesCquot; gt; wrote in message
...
gt; Hi All,
gt;
gt; I am sure this is just a simple question for the gurus here.
gt;
gt; I have recently come accross a spreadsheet with the following formula:
gt;
gt; sum((Column Agt;=X)*(Column Alt;Y))
gt;
gt; The result was a count of everything in column A that has the value
gt; greater
gt; or equal to X while less than Y. It's an interesting approach, but I am
gt; wondering how the operator quot;*quot; is being used? In addition, why use quot;Sumquot;,
gt; and not quot;Countifquot;?
gt;
gt; Thanks,
Wow, nice! Thank you for that insight!