+ Reply to Thread
Results 1 to 8 of 8

Count non consecutive columns

  1. #1
    mkondo@uk.ey.com
    Guest

    Count non consecutive columns

    Hello - hoping someone can help me out.

    I have a worksheet with a number of columns and I am attempting to
    count the occurances where the value is >=100% in columns headed "% of
    Target Achieved" on a row-by-row basis.

    The value in these columns will either be a % (positive or negative) or
    "-"
    Values in other columns that I do not want included are numbers or
    blank.

    Thankyou


  2. #2
    Marcelo
    Guest

    RE: Count non consecutive columns

    Hi,

    Assuming that the data that you would like to count is on the collumn C, so:

    try countif(C2:c1000;">=100%")

    hope it helps

    Regards
    Marcelo - Brazil

    "mkondo@uk.ey.com" escreveu:

    > Hello - hoping someone can help me out.
    >
    > I have a worksheet with a number of columns and I am attempting to
    > count the occurances where the value is >=100% in columns headed "% of
    > Target Achieved" on a row-by-row basis.
    >
    > The value in these columns will either be a % (positive or negative) or
    > "-"
    > Values in other columns that I do not want included are numbers or
    > blank.
    >
    > Thankyou
    >
    >


  3. #3
    mkondo@uk.ey.com
    Guest

    Re: Count non consecutive columns

    Thanks Marcelo - but the data is across a number of columns:
    eg:
    A B C D E F G
    Person A 52 126 42% 36 151 24%
    Person B 0 100% 70 93 75%

    If it makes it easier - it's every 3rd column that I need to get the
    data to count (ie columns D G J M P etc).

    So what I'm trying to end up with is row 1 (Person A) would have a
    count of 0, row 2 would have a count of 1.

    Megan


  4. #4
    Bob Phillips
    Guest

    Re: Count non consecutive columns

    Forgot the second condition, so try

    =SUMPRODUCT(--(B$1:M$1="% of Target achieved"),--(B2:M2>1))

    --
    HTH

    Bob Phillips

    (remove xxx from email address if mailing direct)

    <mkondo@uk.ey.com> wrote in message
    news:1148384726.363973.193510@i39g2000cwa.googlegroups.com...
    > Hello - hoping someone can help me out.
    >
    > I have a worksheet with a number of columns and I am attempting to
    > count the occurances where the value is >=100% in columns headed "% of
    > Target Achieved" on a row-by-row basis.
    >
    > The value in these columns will either be a % (positive or negative) or
    > "-"
    > Values in other columns that I do not want included are numbers or
    > blank.
    >
    > Thankyou
    >




  5. #5
    Bob Phillips
    Guest

    Re: Count non consecutive columns

    =SUMIF(B$1:M$1,"% of Target Achieved",B2:M2)

    and copy down

    --
    HTH

    Bob Phillips

    (remove xxx from email address if mailing direct)

    <mkondo@uk.ey.com> wrote in message
    news:1148384726.363973.193510@i39g2000cwa.googlegroups.com...
    > Hello - hoping someone can help me out.
    >
    > I have a worksheet with a number of columns and I am attempting to
    > count the occurances where the value is >=100% in columns headed "% of
    > Target Achieved" on a row-by-row basis.
    >
    > The value in these columns will either be a % (positive or negative) or
    > "-"
    > Values in other columns that I do not want included are numbers or
    > blank.
    >
    > Thankyou
    >




  6. #6
    mkondo@uk.ey.com
    Guest

    Re: Count non consecutive columns

    Bob - thankyou!! It seems to be counting the occurances of "-" though -
    is there anyway I can have them count as 0?


  7. #7
    Bob Phillips
    Guest

    Re: Count non consecutive columns

    =SUMPRODUCT(--(B$1:M$1="% of Target
    achieved"),--ISNUMBER(B2:M2),--(B2:M2>1))

    --
    HTH

    Bob Phillips

    (remove xxx from email address if mailing direct)

    <mkondo@uk.ey.com> wrote in message
    news:1148388843.926582.222560@i39g2000cwa.googlegroups.com...
    > Bob - thankyou!! It seems to be counting the occurances of "-" though -
    > is there anyway I can have them count as 0?
    >




  8. #8
    mkondo@uk.ey.com
    Guest

    Re: Count non consecutive columns

    Bob - you're a superstar. Thankyou very much.


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1