If or Lookup

[LostNFound]

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.

 

[Bob Phillips]

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

will give the percentage

--

HTH

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

 

[Gary Brown]

In F1 throught F20 put the #s 1 through 20.
In G1, put the following formula AS AN ARRAY.
=SUM((A1:A8=F1)*(D18="Yes")*1)
what should appear in the cell is...
{=SUM((A1:A8=F1)*(D18="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

 

[Gary Brown]

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

 

[LostNFound]

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]

Because I am a twat, and I missed the no yes in your OP. Try

=SUMPRODUCT(--(A1:A100=1),--(D1100<>"yes"))/COUNTIF(A1:A100,1)

BTW, it should return 87.5%

--

HTH

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

 

[Aladin Akyurek]

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

:

Replace the "yes" bit with just "".

 

[Bob Phillips]

No it is me being a twat. I missed the point in your OP about no yes.

Try this

=SUMPRODUCT(--(A1:A100=1),--(D1100<>"yes"))/COUNTIF(A1:A100,1)

BTW it should be 87.5 not 88

--

HTH

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