GOING CRAZY! - Count Functions

keith · Mar 23rd, 2002, 09:31 PM

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

Dave Patton · Mar 23rd, 2002, 09:57 PM

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)

keith · Mar 23rd, 2002, 10:19 PM

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

Aladin Akyurek · Mar 24th, 2002, 12:45 AM

Quote:

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!

In addition to Help, for SUMPRODUCT see also "Condition-driven computing" at:

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

Aladin

keith · Mar 24th, 2002, 10:47 AM

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!

Aladin Akyurek · Mar 24th, 2002, 10:55 AM

Quote:

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 ]

Yogi Anand · Mar 24th, 2002, 11:12 AM

Quote:

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 ]

Aladin Akyurek · Mar 24th, 2002, 11:26 AM

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

Regards,

Aladin

Quote:

On 2002-03-24 10:12, Yogi Anand wrote:

Quote:

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 ]

Yogi Anand · Mar 24th, 2002, 11:32 AM

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 ]

keith · Mar 24th, 2002, 11:41 AM

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 23rd, 2002, 09:31 PM	#1
keith Board Regular Join Date: Mar 2002 Posts: 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..

Mar 23rd, 2002, 09:57 PM	#2
Dave Patton Board Regular Join Date: Feb 2002 Location: Calgary, Alberta Canada Posts: 2,065	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)

Mar 23rd, 2002, 10:19 PM	#3
keith Board Regular Join Date: Mar 2002 Posts: 88	well I am not exactly sure how yet but it seems to work.. THANKS for the help!

Mar 24th, 2002, 10:47 AM	#5
keith Board Regular Join Date: Mar 2002 Posts: 88	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!

Mar 24th, 2002, 11:32 AM	#9
Yogi Anand MrExcel MVP Join Date: Mar 2002 Location: Michigan USA Posts: 11,452	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 ]

Mar 24th, 2002, 11:41 AM	#10
keith Board Regular Join Date: Mar 2002 Posts: 88	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..