# If or Lookup

1. ## If or Lookup

I have a spreadsheet with numbers from 1-20 in column A and a formula that
returns either a yes or 0(blank) in column D. I am trying to set a formula
that would look down A and if it contains a 1 give me the total number of
ones and then the total number of ones with "yes" only then give me the
percentage of 1's with no "yes" Because Yes means you were late and I need to
know the percentage of on time. I need to do this with the 2's in column A
and the 3's in column A and so on until I get to 6's then it will be 6 and
up...
Any help or advice would be greatly appreciated.

2. ## Re: If or Lookup

=SUMPRODUCT(--(A1:A100=1),--(D1:D100="yes"))/COUNTIF(A1:A100,1)

will give the percentage

--

HTH

RP
(remove nothere from the email address if mailing direct)

"LostNFound" <GottaGo2@Hotmail.com> wrote in message
news:8AA2BE81-DD2D-4E19-8E04-DEFA6E758B8A@microsoft.com...
> I have a spreadsheet with numbers from 1-20 in column A and a formula

that
> returns either a yes or 0(blank) in column D. I am trying to set a formula
> that would look down A and if it contains a 1 give me the total number of
> ones and then the total number of ones with "yes" only then give me the
> percentage of 1's with no "yes" Because Yes means you were late and I need

to
> know the percentage of on time. I need to do this with the 2's in column

A
> and the 3's in column A and so on until I get to 6's then it will be 6 and
> up...
> Any help or advice would be greatly appreciated.

3. ## RE: If or Lookup

In F1 throught F20 put the #s 1 through 20.
In G1, put the following formula AS AN ARRAY.
=SUM((A1:A8=F1)*(D1:D8="Yes")*1)
what should appear in the cell is...
{=SUM((A1:A8=F1)*(D1:D8="Yes")*1)}
To make an array, while creating or editing the formula, hit
'Ctrl-Shift-Enter'. { and } brackets will surround the formula if you did it
right (NOTE: you can NOT simply put the brackets in.)
Copy this formula from G1 to G20.
This is the count of 'yes' responses to the number in column F
In H1, put...
=COUNTIF(A1:A8,F1)
This is the count of the number in column F
In I1, put...
=G1/H1
This is the percentage of 'yes' for the number in column F
In J1, put...
=1-I1
This is the percentage of 'no' for the number in column F
Copy the formulas in H, I and J down to row 20.

HTH,
Gary Brown

"LostNFound" wrote:

> I have a spreadsheet with numbers from 1-20 in column A and a formula that
> returns either a yes or 0(blank) in column D. I am trying to set a formula
> that would look down A and if it contains a 1 give me the total number of
> ones and then the total number of ones with "yes" only then give me the
> percentage of 1's with no "yes" Because Yes means you were late and I need to
> know the percentage of on time. I need to do this with the 2's in column A
> and the 3's in column A and so on until I get to 6's then it will be 6 and
> up...
> Any help or advice would be greatly appreciated.

4. ## RE: If or Lookup

Remember to correct for the ranges. In other words, the below example has
data from row 1 to row 8, but your data may be from row 2 to row 2500.
HTH,
Gary Brown

"Gary Brown" wrote:

> In F1 throught F20 put the #s 1 through 20.
> In G1, put the following formula AS AN ARRAY.
> =SUM((A1:A8=F1)*(D1:D8="Yes")*1)
> what should appear in the cell is...
> {=SUM((A1:A8=F1)*(D1:D8="Yes")*1)}
> To make an array, while creating or editing the formula, hit
> 'Ctrl-Shift-Enter'. { and } brackets will surround the formula if you did it
> right (NOTE: you can NOT simply put the brackets in.)
> Copy this formula from G1 to G20.
> This is the count of 'yes' responses to the number in column F
> In H1, put...
> =COUNTIF(A1:A8,F1)
> This is the count of the number in column F
> In I1, put...
> =G1/H1
> This is the percentage of 'yes' for the number in column F
> In J1, put...
> =1-I1
> This is the percentage of 'no' for the number in column F
> Copy the formulas in H, I and J down to row 20.
>
> HTH,
> Gary Brown
>
>
>
>
>
> "LostNFound" wrote:
>
> > I have a spreadsheet with numbers from 1-20 in column A and a formula that
> > returns either a yes or 0(blank) in column D. I am trying to set a formula
> > that would look down A and if it contains a 1 give me the total number of
> > ones and then the total number of ones with "yes" only then give me the
> > percentage of 1's with no "yes" Because Yes means you were late and I need to
> > know the percentage of on time. I need to do this with the 2's in column A
> > and the 3's in column A and so on until I get to 6's then it will be 6 and
> > up...
> > Any help or advice would be greatly appreciated.

5. ## Re: If or Lookup

Is this counting the "yes" only with "1" in column A! Is it looking for both
criterias?
I had eight ones in column A and one yes were there was a one and it
returned 12.5% it should have returned 88%

What did I do wrong??

"Bob Phillips" wrote:

> =SUMPRODUCT(--(A1:A100=1),--(D1:D100="yes"))/COUNTIF(A1:A100,1)
>
> will give the percentage
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "LostNFound" <GottaGo2@Hotmail.com> wrote in message
> news:8AA2BE81-DD2D-4E19-8E04-DEFA6E758B8A@microsoft.com...
> > I have a spreadsheet with numbers from 1-20 in column A and a formula

> that
> > returns either a yes or 0(blank) in column D. I am trying to set a formula
> > that would look down A and if it contains a 1 give me the total number of
> > ones and then the total number of ones with "yes" only then give me the
> > percentage of 1's with no "yes" Because Yes means you were late and I need

> to
> > know the percentage of on time. I need to do this with the 2's in column

> A
> > and the 3's in column A and so on until I get to 6's then it will be 6 and
> > up...
> > Any help or advice would be greatly appreciated.

>
>
>

6. ## Re: If or Lookup

LostNFound wrote:
> Is this counting the "yes" only with "1" in column A! Is it looking for both
> criterias?
> I had eight ones in column A and one yes were there was a one and it
> returned 12.5% it should have returned 88%
>
> What did I do wrong??
>
> "Bob Phillips" wrote:
>
>
>>=SUMPRODUCT(--(A1:A100=1),--(D1:D100="yes"))/COUNTIF(A1:A100,1)
>>
>>will give the percentage

Replace the "yes" bit with just "".

##### Users Browsing this Thread

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