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

[Guest]

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,

 

[Biff]

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

 

[Guest]

Wow, nice! Thank you for that insight!