Error Checking in Excel
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,425
    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
    83,648
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 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
    83,648
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 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
    83,648
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 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..

Some videos you may like

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
  •