How else can "*" be used in a formula and Sum vs. Countif
 

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 A=X)*(Column A<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 "*" is being used? In addition, why use "Sum",
and not "Countif"?

Thanks,

How else can "*" be used in a formula and Sum vs. Countif
 

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:

A1=X = TRUE * A1<Y = FALSE
A2=X = TRUE * A2<Y = TRUE

TRUE * FALSE = 0
TRUE * TRUE = 1

SUM(0,1) = 1

The same formula written with Countif:

=COUNTIF(A1:A10,"=X")-COUNTIF(A1:A10,"Y")

=SUM((A1:A10=X)*(A1:A10<=Y))

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

Biff

"JamesC" wrote in message
...
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 A=X)*(Column A<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 "*" is being used? In addition, why use "Sum",
and not "Countif"?

Thanks,

How else can "*" be used in a formula and Sum vs. Countif
 

Wow, nice! Thank you for that insight!