Count non consecutive columns

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mkondo@uk.ey.com - 23 May 2006 12:45 GMT

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

Reply to this Message

Marcelo - 23 May 2006 13:03 GMT

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

> Hello - hoping someone can help me out.
>
[quoted text clipped - 8 lines]
>
> Thankyou

Reply to this Message

mkondo@uk.ey.com - 23 May 2006 13:31 GMT

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

Reply to this Message

Bob Phillips - 23 May 2006 13:36 GMT

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

and copy down

Signature

HTH

Bob Phillips

(remove xxx from email address if mailing direct)

> Hello - hoping someone can help me out.
>
[quoted text clipped - 8 lines]
>
> Thankyou

Reply to this Message

mkondo@uk.ey.com - 23 May 2006 13:54 GMT

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

Reply to this Message

Bob Phillips - 23 May 2006 15:04 GMT

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

Signature

HTH

Bob Phillips

(remove xxx from email address if mailing direct)

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

Reply to this Message

mkondo@uk.ey.com - 23 May 2006 15:24 GMT

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

Reply to this Message

Bob Phillips - 23 May 2006 13:38 GMT

Forgot the second condition, so try

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

Signature

HTH

Bob Phillips

(remove xxx from email address if mailing direct)

> Hello - hoping someone can help me out.
>
[quoted text clipped - 8 lines]
>
> Thankyou

Reply to this Message