Calculated field with column subitems?

[YounesB3]

Hi, I have a source data which I can't change because if I add a column with a formula, it will make the file that much heavier.

I'd like to calculate a simple ratio of calculations. Here's a simulated example of what I'd like.

Column A	Column B
AB	Yes1
AB	No
EF	-
CD	-
AB	Yes2
CD	-
AB	Yes3
AB	No
AB	Yes1

<tbody>
</tbody>

Basically, I want a calculated field of a ratio of i/j:
i =being a countifs of all the AB and Yes1, Yes2, Yes3
j = being a countif of all the AB.

It would be easy by adding a new column, but I can't. I thought that it would be possible with a calculated field in a pivot table, but I can't find the option to do what I want. Any help would be appreciated.

 

[Joe4]

Maybe this:

=SUMPRODUCT(--(A:A="AB"),--(LEFT(B:B,3)="Yes"))/COUNTIF(A:A,"AB")

 

[YounesB3]

Maybe this:

=SUMPRODUCT(--(A:A="AB"),--(LEFT(B:B,3)="Yes"))/COUNTIF(A:A,"AB")

Hi the "Yes" were examples. It's actually 3 different labels.

Also, this would be the pivot calculated field? You can have ranges in the calculated field?

 

[Joe4]

Just do each individual label and add them up:

=(COUNTIFS(A:A,"AB",B:B,"Yes1")+COUNTIFS(A:A,"AB",B:B,"Yes2")+COUNTIFS(A:A,"AB",B:B,"Yes3"))/COUNTIF(A:A,"AB")

You can replace the entire column references with specific range references, i.e. change A:A to A1:A100, if you like.

I don't really work with pivot calculated fields, so really cannot answer that question for you.

 

[YounesB3]

I don't really work with pivot calculated fields, so really cannot answer that question for you.

Oh ok, I don't think that will work then because I can't really change the source data.[/QUOTE]