Count Function????

[coccio]

I want to be able to count how many up figure and down figures in a range

Like this example:

In column A: I have the Month numbered 1-12
In column B: I have the days numbered 1-31
In column C: I have the years from 1988-2003
In column D: are the numbers ranging from positive to negative.

I want to be able to for example find:


12th month in (column A) and the fist day (Column B) and count how many Column D numbers are up. And also how many are down.

How can that be done?

 

[Yogi Anand]

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))

 

[coccio]

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.

 

[Yogi Anand]

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

 

[Aladin Akyurek]

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

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

where F1 is 12 and F2 1.

 

[Yogi Anand]

Hi Aladin:

Much better than mine!

 

[luckycharm]

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

 

[Aladin Akyurek]

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

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.

 

[luckycharm]

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).

 

[just_jon]

Yes, luckycharm, those are the prefered methods.