# Count non consecutive columns

1. ## 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. ## 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. ## 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. ## 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
> 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. ## 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
> 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. ## 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. ## 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
> Bob - thankyou!! It seems to be counting the occurances of "-" though -
> is there anyway I can have them count as 0?
>

8. ## Re: Count non consecutive columns

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

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

#### 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