GOING CRAZY! - Count Functions
Amazing chart utilities from Jon Peltier
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 26

Thread: GOING CRAZY! - Count Functions

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    88
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,329
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default


    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. #3
    Board Regular
    Join Date
    Mar 2002
    Posts
    88
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,807
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    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

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Posts
    88
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,807
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    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 ]

  7. #7
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,807
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    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 ]

  9. #9
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  10. #10
    Board Regular
    Join Date
    Mar 2002
    Posts
    88
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com