Count Function????

coccio

Board Regular
Joined
Mar 19, 2002
Messages
156
Office Version
  1. 2016
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?
 
luckycharm said:
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! :lol: )
 
Upvote 0

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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.
 
Upvote 0
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).
 
Upvote 0
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! :-D
 
Upvote 0
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! :-D

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
 
Upvote 0
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,
 
Upvote 0
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?
 
Upvote 0
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,
 
Upvote 0

Forum statistics

Threads
1,223,270
Messages
6,171,102
Members
452,379
Latest member
IainTru

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top