Count Function????

Hi coccio:

If I understood you correctly, try for up ...

=SUMPRODUCT(($A$1:$A$50=12)*($B$1:$B$50=1)*($D$1:$D$50>0))

and for down ...

=SUMPRODUCT(($A$1:$A$50=12)*($B$1:$B$50=1)*($D$1:$D$50<0))

Thanks that did work. But I was wondering can you get a total of all the ups and down figures using one formula. Like sumproduct or countif. I want it to count both up and down figures.

Hi coccio:

How about ...

=SUMPRODUCT(($A$1:$A$50=12)*($B$1:$B$50=1)*ISNUMBER(($D$1:$D$50)))

This will give a count of all the positive and negative numbers for Month of 12, and Day 1, including numbers that are =0.

and

=SUMPRODUCT(($A$1:$A$50=12)*($B$1:$B$50=1)*(($D$1:$D$50>0)+($D$1:$D$50<0)))

will exclude 0s

coccio said:

...I want it to count both up and down figures.

Click to expand...

=SUMPRODUCT(--(A2:A20=F1),--(B2:B20=F2),--(ABS(D2:D20)>0))

where F1 is 12 and F2 1.

Hi Aladin:

Much better than mine!

Sorry for nagging,
but why is the --
is it less expensive than the * ?
else?

luckycharm said:

Sorry for nagging,
but why is the --
is it less expensive than the * ?
else?

Click to expand...

Expensiveness in ascending order for the oft-used coercers:

--

+0

*1

Plus Zero is closer to double Nots.

Note that all math operations cause coercion lke in

=TRUE^10

Recall also that the syntax for SumProduct is...

SumProduct(X1,X2,...)

where each X must be either a numerical range or a numerical array object.

Given the above syntax,

=SUMPRODUCT(A2:A10,B2:B10)

is faster than

{=SUM(A2:A10*B2:B10)}

but also faster than

=SUMPRODUCT(A2:A10*B2:B10)

where both A2:A10and B2:B10are numerical range objects.

In multiconditional computations (say multiconditional counting), we have often logical array objects to consider, e.g.,

E2:E10="UP" and F2:F10=2003

Lets name such expressions conditionals.

[1]

{=SUM((E2:E10="UP")*(F2:F10=2003))}

would give the desired multiconditional count as would:

[2]

=SUMPRODUCT((E2:E10="UP")*(F2:F10=2003))

[3]

=SUMPRODUCT(--(E2:E10="UP"),--(F2:F10=2003))

[4]

=SUMPRODUCT((E2:E10="UP")+0,(F2:F10=2003)+0)

[5]

=SUMPRODUCT((E2:E10="UP")*1,(F2:F10=2003)*1)

In [1] and [2], the coercion occurs as a side-effect of a mathematical operation (a multiplication as indicated by *).

In [3] to [5], we first coercethe arrays of logical values as result of the conditional evaluations into numerical arrays (as SumProduct needs/expects them), which then get multiplied by SumProduct (the comma in the syntax of SumProduct stands for "vector multiplication") then summed.

The timing results show that coercion by -- leads to less jumpy computations. That is, each run shows the same timing characteristics, compared to a coercion with +0 (coercion with *1 is slower). Average time are almost the same for SumProduct formulas that use these coercers.

The SumProduct formulas would be much faster if SumProduct would implicitly coerce (that is, without the need for an explicit coercer like -- or +0) the results of conditional evaluations into numbers.

Well coerced (oops, explained). Thanks

So (to be sure), always best (if possible) is
=SUMPRODUCT(A2:A10,B2:B10)
if coercing is needed, then use:
=SUMPRODUCT(--(E2:E10="UP"),--(F2:F10=2003))

Where were you in my school days? (don't ask when that was).

Yes, luckycharm, those are the prefered methods.