Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 3 of 3 FirstFirst 123
Results 21 to 26 of 26

Thread: GOING CRAZY! - Count Functions

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

    Default

    must have been syntax.. Or I think I may have overlaped columns or something by mistake.. works now.. thanks..

  2. #22
    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-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?
    Hey Keith and Aladin:
    Looks like we are really beating at it. Keith I am not trying to convince you to use the DCOUNT function but it does work.Here I have used the data used in Aladin's example with the criteria being that Date is 37338 and Item1=2 tocount the number of items meeting the criteria in item1 column, and then use the DCOUNT function again with the criteria that Date is 37338 and item2=2 ...
    so here is what we will get

    =DCOUNT(A1:C4,"item1",F1:G2)+DCOUNT(A1:C4,"Item2",H1:I2)
    resulting in 3

    T H A N K S ! for keeping this discussion alive.

    Please post back if it works for you ... otherwise explain a little further and let us take it from there.
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  3. #23
    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 15:20, Yogi Anand wrote:
    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?
    Hey Keith and Aladin:
    Looks like we are really beating at it. Keith I am not trying to convince you to use the DCOUNT function but it does work.Here I have used the data used in Aladin's example with the criteria being that Date is 37338 and Item1=2 tocount the number of items meeting the criteria in item1 column, and then use the DCOUNT function again with the criteria that Date is 37338 and item2=2 ...
    so here is what we will get

    =DCOUNT(A1:C4,"item1",F1:G2)+DCOUNT(A1:C4,"Item2",H1:I2)
    resulting in 3

    T H A N K S ! for keeping this discussion alive.

    Please post back if it works for you ... otherwise explain a little further and let us take it from there.
    What Keith does is

    =SUMPRODUCT((date-range=a-date-crit)*(complete-items-range=num-crit))

    where

    date-range can be, say, A2:A600 and

    complete-items-range can be B2:Z600 or beyond column Z which is tested for meeting a single num crit, e.g., 2.

    That would make lots +DCOUNT...+DCOUNT... unless there is some short-cut to express what he succeeded to express in a single SUMPRODUCT formula. See his exchange with me.

    Aladin


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

    Default

    I am not tryting to avoid DCOUNT. The DCOUNT Function does work but it requires a formula with "DCOUNT()+DCOUNT()+DCOUNT()+DCOUNT()+DCOUNT() for all my different items (there are many) whereas with the SUMPRODUCT I can use a small formula like "=SUMPRODUCT((data!A2:A3=E2)*(data!B2:AT35=F2))".. In addition my criteia is 2 cells TOTAL verses a critera of a 2 cells minimum for every DCOUNT.. follow me? It all seems to work now - Thanks for the help.

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

    Default

    LOL - me and Aladin said the same thing - must have been typing at same time

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

    Default

    T H A N K S ! Keith and Aladin:
    That settles it -- thanks for the clarification.

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
  •