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

Thread: GOING CRAZY! - Count Functions

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

    Default

     
    On 2002-03-24 10:32, Yogi Anand wrote:
    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 ]
    How do you not include the split vertical bars in the database?

    I duplicated but found 3 not 2.

  2. #12
    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 10:41, keith wrote:
    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..
    Keith,

    I'm confused. AND'ing the way you do would require that Item1 as well as Item2 must meet the condition of being 2. So when Item1 is 2 but Item2 not, the count will not include the target row. Extending the criteria for DCOUNT will behave the same.

    Aladin

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

    Default

    the split vertical bars or "|" i put in the initial post were just for the example. I just used them to demonstrate column breaks in the post.

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

    Default

    well it seems to work (I hope its not just working for some wierd coincidence).. my end goal is to count everytime a "2" shows up in ANY ITEM COLUMN where a specific date is met in the DATA COLUMN. Keep in mind I am not counting rows but cells that contain "2". So if the date matches and ITEM 1=2 and ITEM2=2 then the formula result should be "2". If item1=1 and item2=2 the result should be "1" and vice versa.. Am I all messed up?

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

    Default

    http://www.strategiccs.com/sumproduct.xls

    this is the example i am working with if it helps..

  6. #16
    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 11:05, keith wrote:
    well it seems to work (I hope its not just working for some wierd coincidence).. my end goal is to count everytime a "2" shows up in ANY ITEM COLUMN where a specific date is met in the DATA COLUMN. Keep in mind I am not counting rows but cells that contain "2". So if the date matches and ITEM 1=2 and ITEM2=2 then the formula result should be "2". If item1=1 and item2=2 the result should be "1" and vice versa.. Am I all messed up?
    Lets say that A1:C4 houses the following sample:

    {"Date","Item1","Item2";
    37338,2,2;
    37340,5,2;
    37338,2,8}

    and F2:G2 houses

    {37338,2}

    According to your current specs, we should get 2 as result.

    [1]

    =SUMPRODUCT((A2:A4=F2)*(A2:C4=2))

    will produce 3.

    [2], which will I'm afraid discourage you,

    =SUMPRODUCT((A2:A4=F2)*((B2:B4=G2)+(C2:C4=G2)))-SUMPRODUCT((A2:A4=F2)*(B2:B4=G2)*(C2:C4=G2))

    will compute the right answer.

    Aladin


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

    Default

    (Your Example)
    Lets say that A1:C4 houses the following sample:

    {"Date","Item1","Item2";
    37338,2,2;
    37340,5,2;
    37338,2,8}

    and F2:G2 houses

    {37338,2}

    According to your current specs, we should get 2 as result.

    - Let me break in here.. according to my needs I shoud get "3" as a result not "2" because I need to count EVERY CELL that houses a "2" when the date is met.. so in the above example "2" is listed 3 total times when 37338 is in the date field.. see what I mean?

  8. #18
    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 11:41, keith wrote:
    (Your Example)
    Lets say that A1:C4 houses the following sample:

    {"Date","Item1","Item2";
    37338,2,2;
    37340,5,2;
    37338,2,8}

    and F2:G2 houses

    {37338,2}

    According to your current specs, we should get 2 as result.

    - Let me break in here.. according to my needs I shoud get "3" as a result not "2" because I need to count EVERY CELL that houses a "2" when the date is met.. so in the above example "2" is listed 3 total times when 37338 is in the date field.. see what I mean?
    Keith,

    I now hear you...
    The wording made me a bit nervous at first... But, at the end, you're right in your judgement about the use you've put SUMPRODUCT to.

    Regards,

    Aladin

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

    Default

    Sorry - had to step away for a few.. is there any problem with SUMPRODUCT when the data is on a different sheet?

  10. #20
    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 14:51, keith wrote:
    Sorry - had to step away for a few.. is there any problem with SUMPRODUCT when the data is on a different sheet?
    No. Did you encounter a specific problem?

    Aladin

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