If A1=this or that or even the other?????

[B.H.JIG]

I am trying to produce an accuracy report (we'll call spreadsheet 2) that
updates as i type in my information on main sheet (spreadsheet 1.)
Spreadsheet 2 would need to recognise the "company name" (for example we'll
use the phonetic alphabet) for column A and a "y" or "n" for another column B
to indicate whether or not the delivery is correct or not.

Spreadsheet 1
A B
1 company correct
2 alpha y
3 bravo y
4 charlie n
5 alpha n
6 alpha y
7 bravo n
8 charlie y
9 charlie y
10 charlie y

Would there be a formula that enables spreadsheet 2 to show the % of correct
alpha deliveries, and Bravo etc. Using the information in spreadsheet 1(ss1),
i would like spreadsheet 2(ss2) to look like the example below.

Spreadsheet 2
A B C
1 company total deliveries % correct
2 alpha 3 66.66
3 bravo 2 50
4 charlie 4 75

Is there such a function available in Excel that "IF A2 on ss1=alpha then
add 1 to B2 on ss2, if A2 on ss1=bravo then add 1 to B3 on ss2, if A2 on
ss1=charlie then add 1 to B4 on ss2" ETC. Then having it recognise wether or
not the delivery was correct, then working out a correct %.

Any help on this matter would be very much appreciated.

 

[Max]

Assuming source data in Sheet1, cols A and B

In Sheet2,

Put in B2: =COUNTIF(Sheet1!A:A,A2)
Put in C2:
=SUMPRODUCT((Sheet1!$A$2:$A$10=A2)*(Sheet1!$B$2:$B$10="y"))/B2
Format C2 as percentage? to taste. Adapt the ranges to suit.
Then select B2:C2, fill down as far as required

 

[pinmaster]

Hi

Try something like this:

=SUMPRODUCT((A1:A10="charlie")*(B1:B10="y"))/COUNTIF(A1:A10,"charlie")

format the cell as % and use the countif fomrula to get a total number of
deliveries.

HTH
Jean-Guy