Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: sorting with a countif needed

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

    Default

    I'm trying to sort a ten column report


    Col 1 Col 2 Col 3
    elaps. elaps
    Place Time Place Time Place Time
    Row 1 ab 1 hr xy 2 hrs ab 1
    row 2 xy 1 hr ab 1 hr xy 1

    There can be ten different kinds of places, ie: ab, xy, cd, fa, etc.

    I need to be able to have a formula that will add two or more times together If the location is the same in the same row. Then I need to count that result only if it is greater than one hour. Ie in row one if ab appears twice then i need to add their times. As per above that would be "ab" appears twice and it is greater than an hour. xy appears once and it is > an hour . Total count for ab for that row is 1. Total count for xy =1.

    But I have ten different locations that i will have to analyze.

    Help!

  2. #2
    Board Regular
    Join Date
    Apr 2002
    Posts
    100
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    wow, my data came over all jumbled up as per how the table looks. Sorry. Hopefully the question will make sense. How do i post a small sample of a sheet?

    dawn

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,063
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default

    Do you have time in a cell as number like

    2

    or

    2 hrs


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

    Default

    just 2

    Thnks,
    Dawn

  5. #5
    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-04-25 09:37, dawnfoss wrote:
    I'm trying to sort a ten column report


    Col 1 Col 2 Col 3
    elaps. elaps
    Place Time Place Time Place Time
    Row 1 ab 1 hr xy 2 hrs ab 1
    row 2 xy 1 hr ab 1 hr xy 1

    There can be ten different kinds of places, ie: ab, xy, cd, fa, etc.

    I need to be able to have a formula that will add two or more times together If the location is the same in the same row. Then I need to count that result only if it is greater than one hour. Ie in row one if ab appears twice then i need to add their times. As per above that would be "ab" appears twice and it is greater than an hour. xy appears once and it is > an hour . Total count for ab for that row is 1. Total count for xy =1.

    But I have ten different locations that i will have to analyze.

    Help!
    Hi Dawn:
    If I have understood you correctly, and
    your 2 rows of data is in cells A1:F3, then use the following formula in cell G2 and drag down

    =B2+IF(A2<>C2,0,IF(B2>1,B2,0))+IF(A2<>E2,0,IF(B2>1,B2,0))

    with your data it will result in 1 in cell G2, and 1 in cell G3

    HTH

    Regards!

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

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,063
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default

    On 2002-04-25 10:46, dawnfoss wrote:
    just 2

    Thnks,
    Dawn
    I'll assume that A1:F3 houses the sample data you provided:

    {"Place","Time","Place","Time","Place","Time;
    "ab",1,"xy",2,"ab",1;
    "xy",1,"ab",1,"xy",1}

    Enter unique place names in row 1 from G1 on. For the sample we have:

    ab in G1, and
    xy in H1.

    In G2 enter:

    =(SUMPRODUCT(($A2:$E2=G$1)*(ISNUMBER($B2:$F2)),$B2:$F2)>1)+0

    Copy this first to H2 then down.

    Aladin


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
  •