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?

 

[Aladin Akyurek]

if my data is in a different sheet i need to use the inderect formula to make reference to that sheet in order for the formula to work?

here is the formula:

=SUMPRODUCT(--(INDIRECT("West!$C$5:$C$50"):INDIRECT("West!$C$5:$C$50")=Total!$A$1),--(INDIRECT("West!$e$5:$e$50"):INDIRECT("West!$e$5:$e$50")=Total!$A4))

in the sheet west is the data that i need to count
but in the sheet total are the conditions for the count area.

i tryied using the formula without the indirect function but it didnt work.. by adding the inderect function it work. Do you know why??

thanks!

Are you saying that:

=SUMPRODUCT(--(West!$C$5:$C$50=Total!$A$1),--(West!$E$5:$E$50=Total!$A4))

does not work?

 

[sucett]

Are you saying that:

=SUMPRODUCT(--(West!$C$5:$C$50=Total!$A$1),--(West!$E$5:$E$50=Total!$A4))

does not work?

it works.. but the formula was devoloped by me and i want to learn why the inderect is needing..

 

[Aladin Akyurek]

Are you saying that:

=SUMPRODUCT(--(West!$C$5:$C$50=Total!$A$1),--(West!$E$5:$E$50=Total!$A4))

does not work?

INDIRECT was either a mistake (brings in a dose of inefficiency) or it was probably used to lock the formula to the ranges in question.

 

[spcalan]

Good Explaination on the --, but I was completely lost...

 

[Aladin Akyurek]

Good Explaination on the --, but I was completely lost...

If you need more...

http://www.mrexcel.com/forum/showthread.php?t=128907

 

[Stormseed]

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

Hi Aladin,

Could you please let me know why did you use the double unary to coerce the numeric expression in column range F here ?

I understand what you are trying to explain but a bit of confusion

 

[Aladin Akyurek]

Hi Aladin,

Could you please let me know why did you use the double unary to coerce the numeric expression in column range F here ?

I understand what you are trying to explain but a bit of confusion

The last term in

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

is a conditional (with an equality test) whose evaluation yields a bunch of TRUE/FALSE's. The -- bit in front of this conditional transforms those TRUE/FALSE results into 1/0's, as needed in the syntax the way the above formula embeds the SumProduct function.

 

[Stormseed]

thanks but not understood

What do you exactly mean by conditional ?

If I understand correctly, you mean to say, whenever I force a number to return a boolean value, I have to use the double unary else I do not need to, correct ?

 

[Aladin Akyurek]

thanks but not understood

What do you exactly mean by conditional ?

The term

F2:F10=2003

is a conditional in that it tests whether a cell in the range of F2:F10 equals 2003. Another way of expressing such a term is:

=IF(F2:F10=2003...

In both forms we get either a TRUE or a FALSE.

With:

--(F2:F10=2003)

we get either 1 or 0. This we call: Coercion. That is coercion from logical (boolean) to numeric.

BTW, the IF version becomes:

=IF(F2:F10=2003,1,0)

If I understand correctly, you mean to say, whenever I force a number to return a boolean value, I have to use the double unary else I do not need to, correct ?

No, we speak of coercion from logical (boolean) or text-number to number, not vice versa.