SumProduct or Countif

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.

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)

"Dennis Saunders" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
>

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

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)


--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Dennis Saunders" <[email protected]> wrote in message
news:[email protected]...
>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.
>

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)

"Dennis Saunders" <[email protected]> wrote in message
news:[email protected]...
> Bob....5 entries in Col B for 1.20 .....3 of them have X in Col A so 3/5 =
> 60%
> Regards
>
>

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