GOING CRAZY! - Count Functions

keith

Board Regular
Joined
Mar 3, 2002
Messages
88
Example Data:
Date Column | Item1 Col | Item2 Col
2002/03/23 | 2 | 5
2002/03/25 | 5 | 6
2002/03/23 | 2 | 5
many more rows of similar...

Example Problem: - I need to count the number of times a "2" is listed in the "item 1" column when the date in "date column" is "2002/03/23" for the same row etc.. (2 would be above result)

I am pretty sure this is beyond simple COUNTIF and I can't seem to make DCOUNT work either. A DCOUNTIF would be perfect but it doesn't exist that I am aware of.. VBA maybe? or maybe I am missing something on COUNTIF or DCOUNT? - Please help - thanks in advance..
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Either of the following will give you the count.

With criteria in E2 and F2

=SUMPRODUCT((A2:A6=E2)*(B2:B6=F2))

=DCOUNT(A1:C6,"Item1",E1:F2)
 
Upvote 0
YES! The info helped tremdously. I am still a litte confused on SUMPRODUCT (or arrays in general) but I think I have the basics of it. I have it figured out enough to complete my current project anyway! Thanks for the help - this board has been good to me - I hope that one day I will be able to give some help back to others!
 
Upvote 0
On 2002-03-24 09:47, keith wrote:
YES! The info helped tremdously. I am still a litte confused on SUMPRODUCT (or arrays in general) but I think I have the basics of it. I have it figured out enough to complete my current project anyway! Thanks for the help - this board has been good to me - I hope that one day I will be able to give some help back to others!

Keith,

Glad to hear that... I'd suggest studying the database functions too.

Regards,

Aladin
This message was edited by Aladin Akyurek on 2002-03-24 09:55
 
Upvote 0
On 2002-03-23 20:31, keith wrote:
Example Data:
Date Column | Item1 Col | Item2 Col
2002/03/23 | 2 | 5
2002/03/25 | 5 | 6
2002/03/23 | 2 | 5
many more rows of similar...

Example Problem: - I need to count the number of times a "2" is listed in the "item 1" column when the date in "date column" is "2002/03/23" for the same row etc.. (2 would be above result)

I am pretty sure this is beyond simple COUNTIF and I can't seem to make DCOUNT work either. A DCOUNTIF would be perfect but it doesn't exist that I am aware of.. VBA maybe? or maybe I am missing something on COUNTIF or DCOUNT? - Please help - thanks in advance..

Hi Keith:
The DCOUNT function does work ...
I have reproduced your table in cells G8:I11 (without the split bars as column-breakers), I have my creiteria in cells H5:H6 -- then

=DCOUNT(G8:I11,2,H5:H6) results in 2

Please post back if it works for you ... otherwise explain what are you doing in DCOUNT function and let us take it from there!



_________________
Yogi Anand
Edit: Deleted inactive web site reference from hard coded signature
This message was edited by Yogi Anand on 2003-01-19 15:25
 
Upvote 0
I believe Dave Patton already proposed the DCOUNT solution, in case you might have not noticed.

Regards,

Aladin

On 2002-03-24 10:12, Yogi Anand wrote:
On 2002-03-23 20:31, keith wrote:
Example Data:
Date Column | Item1 Col | Item2 Col
2002/03/23 | 2 | 5
2002/03/25 | 5 | 6
2002/03/23 | 2 | 5
many more rows of similar...

Example Problem: - I need to count the number of times a "2" is listed in the "item 1" column when the date in "date column" is "2002/03/23" for the same row etc.. (2 would be above result)

I am pretty sure this is beyond simple COUNTIF and I can't seem to make DCOUNT work either. A DCOUNTIF would be perfect but it doesn't exist that I am aware of.. VBA maybe? or maybe I am missing something on COUNTIF or DCOUNT? - Please help - thanks in advance..

Hi Keith:
The DCOUNT function does work ...
I have reproduced your table in cells G8:I11 (without the split bars as column-breakers), I have my creiteria in cells H5:H6 -- then

=DCOUNT(G8:I11,2,H5:H6) results in 2

Please post back if it works for you ... otherwise explain what are you doing in DCOUNT function and let us take it from there!



_________________
Yogi Anand
ANAND Enterprises
http://www.handtech.com/anand yogia@hotmail.com
This message was edited by Yogi Anand on 2002-03-24 10:13
 
Upvote 0
Hi Aladin:
You are right (I noticed it after my posting)-- my point was Keith said DCOUNT did not work for him ( he wished there would be something like a DCOUNTIF function) so I was wondering what he did that made the function not work (perhaps he may have included the split vertical bars as part of his data ... just guessing!) T H A N K S!
This message was edited by Yogi Anand on 2002-03-24 10:33
 
Upvote 0
yea - I got the DCOUNT to work after Dave posted, but in the total scope of the project I think the SUMPRODUCT works better as I need to scan multiple "item" columns for a specified # based on date. For example I need to return how many total "2"'s there are in Item1 AND Item2 and so on for a specific date. SUMPRODUCT seems to do this for me.. Thanks for responding though.. the formula I am using applicable to the initial example would be - =SUMPRODUCT((A2:A6=E2)*(B2:C6=F2)) (notice that b2:c6 would be both Item1 and Item2 data...) and I have 48 items to extend the range to cover..
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

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