Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: FOR YOGI and ALADYN - YOUR'E RIGHT.....

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

    Default

    Yogi, You are right, this is what I am looking for but the formula that Aladyn provided only counts 5 and the answer should be six as illustrated in the original post:
    http://mrexcel.com/board/viewtopic.p...4021&forum=2&5

    below is your response. You explained what I am trying to do correctly. I explained it poorly in my original post..... help!!


    Eddie,

    If I've read this correctly you want the count of any times in either A or B BUT NOT both, so:

    A no time and B no time = 0
    A time and B no time = 1
    A no time and B time = 1
    A time and B time ALSO = 1

    if this is the case then Aladin's formula is the one to go with.


  2. #2
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-04 14:37, Eddie G. wrote:
    Yogi, You are right, this is what I am looking for but the formula that Aladyn provided only counts 5 and the answer should be six as illustrated in the original post:
    http://mrexcel.com/board/viewtopic.p...4021&forum=2&5

    below is your response. You explained what I am trying to do correctly. I explained it poorly in my original post..... help!!


    Eddie,

    If I've read this correctly you want the count of any times in either A or B BUT NOT both, so:

    A no time and B no time = 0
    A time and B no time = 1
    A no time and B time = 1
    A time and B time ALSO = 1

    if this is the case then Aladin's formula is the one to go with.

    Eddie, I tried Aladin's formula and it worked fine for me. Is your data exactly as posted at
    http://mrexcel.com/board/viewtopic.p...4021&forum=2&5

    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    OKC
    Posts
    98
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Barrie, You are right, it works in that case but it doesn't work in the following case, the answer i want is 4 but aladyn's formula results in 3........

    col a col b
    1 1769
    2 1748 1784
    3 1755
    4 1777

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    OKC
    Posts
    98
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    well, it appears that the forum is not showing up the way i am typing it....the bottom line is that the count in col a may or may not be more than col b....i need the count of the number of rows that contain either a value in A or B or both as one.

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-04 14:37, Eddie G. wrote:
    Yogi, You are right, this is what I am looking for but the formula that Aladyn provided only counts 5 and the answer should be six as illustrated in the original post:
    http://mrexcel.com/board/viewtopic.p...4021&forum=2&5

    below is your response. You explained what I am trying to do correctly. I explained it poorly in my original post..... help!!


    Eddie,

    If I've read this correctly you want the count of any times in either A or B BUT NOT both, so:

    A no time and B no time = 0
    A time and B no time = 1
    A no time and B time = 1
    A time and B time ALSO = 1

    if this is the case then Aladin's formula is the one to go with.

    Yogi NOTHING, I wrote this response!!!???? Ian Mac
    Hope you got your answer.

    Barrie if you have time I have a nice VBA problem I'd like to present to you, may I E.mail???
    "Have a good time......all the time"
    Ian Mac

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I've tried Aladin formula on the example No.'s you've given and 4 is the result????
    "Have a good time......all the time"
    Ian Mac

  7. #7
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,613
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-04 15:23, Eddie G. wrote:
    Barrie, You are right, it works in that case but it doesn't work in the following case, the answer i want is 4 but aladyn's formula results in 3........

    col a col b
    1 1769
    2 1748 1784
    3 1755
    4 1777
    In that case, use e.g.,

    =SUMPRODUCT((1*(ISNUMBER(A1:A4)+ISNUMBER(B1:B4))))-SUMPRODUCT(ISNUMBER(A1:A4)*ISNUMBER(B1:B4))

    Aladin

  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-04 15:38, Aladin Akyurek wrote:
    On 2002-04-04 15:23, Eddie G. wrote:
    Barrie, You are right, it works in that case but it doesn't work in the following case, the answer i want is 4 but aladyn's formula results in 3........

    col a col b
    1 1769
    2 1748 1784
    3 1755
    4 1777
    In that case, use e.g.,

    =SUMPRODUCT((1*(ISNUMBER(A1:A4)+ISNUMBER(B1:B4))))-SUMPRODUCT(ISNUMBER(A1:A4)*ISNUMBER(B1:B4))

    Aladin
    Don't be smart!! your original formula has worked bothed time for me inc. the second. Why isn't it working for Eddie, is that not the question?
    "Have a good time......all the time"
    Ian Mac

  9. #9
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,613
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-04 15:44, Ian Mac wrote:
    On 2002-04-04 15:38, Aladin Akyurek wrote:
    On 2002-04-04 15:23, Eddie G. wrote:
    Barrie, You are right, it works in that case but it doesn't work in the following case, the answer i want is 4 but aladyn's formula results in 3........

    col a col b
    1 1769
    2 1748 1784
    3 1755
    4 1777
    In that case, use e.g.,

    =SUMPRODUCT((1*(ISNUMBER(A1:A4)+ISNUMBER(B1:B4))))-SUMPRODUCT(ISNUMBER(A1:A4)*ISNUMBER(B1:B4))

    Aladin
    Don't be smart!! your original formula has worked bothed time for me inc. the second. Why isn't it working for Eddie, is that not the question?
    Ian,

    Eddie's specs are much clearer now. For what follows he wants a count of 4 as result.

    {"",1769;
    1748,1784;
    1755,"";
    1777,""}

    The MAX formula will produce a count of 3 here, as Eddie observes. His question boils down to, How many 2-cell rows are there in the target range that contain at least a number (or date)?

    So, the new formula appeared to me the right thing to do.

    Aladin

  10. #10
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-04 16:11, Aladin Akyurek wrote:
    On 2002-04-04 15:44, Ian Mac wrote:
    On 2002-04-04 15:38, Aladin Akyurek wrote:
    On 2002-04-04 15:23, Eddie G. wrote:
    Barrie, You are right, it works in that case but it doesn't work in the following case, the answer i want is 4 but aladyn's formula results in 3........

    col a col b
    1 1769
    2 1748 1784
    3 1755
    4 1777
    In that case, use e.g.,

    =SUMPRODUCT((1*(ISNUMBER(A1:A4)+ISNUMBER(B1:B4))))-SUMPRODUCT(ISNUMBER(A1:A4)*ISNUMBER(B1:B4))

    Aladin
    Don't be smart!! your original formula has worked bothed time for me inc. the second. Why isn't it working for Eddie, is that not the question?
    Ian,

    Eddie's specs are much clearer now. For what follows he wants a count of 4 as result.

    {"",1769;
    1748,1784;
    1755,"";
    1777,""}

    The MAX formula will produce a count of 3 here, as Eddie observes. His question boils down to, How many 2-cell rows are there in the target range that contain at least a number (or date)?

    So, the new formula appeared to me the right thing to do.

    Aladin
    MUST apologise, when I saw the e.g. I was JUST looking at the forum view.

    It's just this minute that I've realised using the [q]quote[/q] thingy you get the tabs also!!!!!!!.

    I saw:

    col a col b
    1 1769
    2 1748 1784
    3 1755
    4 1777

    and you can quote me

    I didn't see the "" in the:

    {"",1769;
    1748,1784;
    1755,"";
    1777,""}

    sorry about that.
    "Have a good time......all the time"
    Ian Mac

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
  •