Thanks:  0
Likes:  0

# Thread: GOING CRAZY! - Count Functions

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

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

3. well I am not exactly sure how yet but it seems to work.. THANKS for the help!

4. On 2002-03-23 21:19, keith wrote:
well I am not exactly sure how yet but it seems to work.. THANKS for the help!

http://www.mrexcel.com/wwwboard/messages/8961.html

5. 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!

6. 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,

[ This Message was edited by: Aladin Akyurek on 2002-03-24 09:55 ]

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

8. I believe Dave Patton already proposed the DCOUNT solution, in case you might have not noticed.

Regards,

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 ]

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 ]

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

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•