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?

 

[Todd Bardoni]

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

If Aladin says its the best way, then you can be sure that it is the known best way of doing it (unless Aladin comes up with an even better way! )

 

[luckycharm]

There never was any doubt in my question that you quoted!

 

[metalkitty]

Thanks for your help!

 

[99flake]

Not pretending to fully understand the use of "--" in SUMPRODUCT(), I am nevertheless using it on a regular basis.

Can anyone tell me why I cannot define a full column, e.g.:

=SUMPRODUCT(--(Detail!D:D='SR Summary'!A2),--(Detail!E:E='SR Summary'!B1))

delivers #NUM! error, whilst:

=SUMPRODUCT(--(Detail!D1:D64000='SR Summary'!A2),--(Detail!E1:E64000='SR Summary'!B1))

works just fine for multi-conditional COUNTIF() type analysis.

 

[Todd Bardoni]

Yep, you cannot use whole column ranges with SUMPRODUCT...just the way it was programmed.

The "--" turns a TRUE into 1 and a FALSE into 0. So, if you have a condition, say, A=B, and you wrap "--()" around it, it will return, first, TRUE/FALSE, and then the TRUE/FALSE is multiplied by the double negative giving a 1/0 result.

So that you can see for yourself, in any cell type =--(TRUE) and =--(FALSE).

 

[lewis_a1]

Multi Conditional Counting Question

Hello, I have been tring to consolidate a spreadsheet of data to another roll up spreadsheet. For example, I would like to count the number of names in cell a, if cell b = 1 and c = call. What command should I used? I have tried sumproduct, if and have not had any success.

Any assitance would be greatly appreciated!

 

[Aladin Akyurek]

Re: Multi Conditional Counting Question

Hello, I have been tring to consolidate a spreadsheet of data to another roll up spreadsheet. For example, I would like to count the number of names in cell a, if cell b = 1 and c = call. What command should I used? I have tried sumproduct, if and have not had any success.

Any assitance would be greatly appreciated!

On the roll-up sheet...

=SUMPRODUCT(--(Sheet1!$A$2:$A$100=A2),--(Sheet1!$B$2:$B$100=B2),--(Sheet1!$C$2:$C$100=C2))

with conditions in:

A2: John

or any other name of interest.

B2: 1

C2: Call

 

[lewis_a1]

Aladin, thank you for the help. My formula creates NAME. I am trying to count all of the data in cell A if the 2 conditions are met in cells b and c. Do I need to add something?

Thanks again,

 

[Aladin Akyurek]

Aladin, thank you for the help. My formula creates NAME. I am trying to count all of the data in cell A if the 2 conditions are met in cells b and c. Do I need to add something?

Thanks again,

What did you try exactly?

 

[lewis_a1]

Here is the formula in the roll up:

=SUMPRODUCT(--('Weekly Activities'!$B$7:$B$85=2),--('Weekly Activities'!$C$7:$C$85=prospect))

Column a has names, b has numbers, c has type of call. I am trying to count column a given 2 conditions in b and c.

Thanks again,