SumProduct or Countif

D

Dennis Saunders

I have 2 columns.
In column A the contents are X, Y or Z only.
In column B a range of rounded values from +3.00 to -3.00.
I want to display the %age of X for each value in column B (and then filter
and graph the results from Column C).

ie
A B C
.... ...... .......
X 1.20
Y 1.20
X 1.20
Z 1.20
X 1.20 60%
X 1.30

I can't figure the syntax (or maybe I should modify a SubTotal ??)
Any Help gratefully received.
 
B

Bob Phillips

Can you explain where 60% comes from? I could see a case for 24.5%, 57%, but
I don't see 60%.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
D

Dennis Saunders

Bob....5 entries in Col B for 1.20 .....3 of them have X in Col A so 3/5 =
60%
Regards
 
B

Bernard Liengme

With data in A1:B6 and
E1 = X, F1 = 1.2
first result
=(SUMPRODUCT(--($B$1:$B$6=F1),--($A$1:$A$6=E1)))/COUNTIF($B$1:$B$6,F1)
 
B

Bob Phillips

I see then, try

=SUMPRODUCT(--($A$1:$A$6=A1),--($B$1:$B$6=B1))/COUNTIF($B$1:$B$6,B1)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
D

Dennis Saunders

Thanks a lot.
Its those double minuses (--) that I missed out (among other things).
I had something like:
=IF(AD23>AD24,SUMPRODUCT(AB:AB="HW")*(AD:AD=AD23)/COUNTIF(AD:AD,AD23),"")
And now its
=IF(AD64>AD65,SUMPRODUCT(--($AB$1:$AB64="HW"),--($AD$1:$AD64=AD64))/COUNTIF($AD$1:$AD64,AD64),"")

Hmm just to graph it and get a formula for the curve.
Happy New Year
Dennis
 
Top