Combine Sum Frequency If functions

[BWMagee]

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'; color: #000000}span.s1 {color: #006107}span.s2 {color: #ab30d6}span.s3 {color: #33af4a}</style>Hi, I'm wondering how to combine the following into one workable function:


[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD][/TD]
[TD]U[/TD]
[TD]V[/TD]
[TD]W[/TD]
[TD]X[/TD]
[TD]Y[/TD]
[TD]Z[/TD]
[/TR]
[TR]
[TD]134[/TD]
[TD]LL[/TD]
[TD]PP[/TD]
[TD][/TD]
[TD]4[/TD]
[TD]-2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]135[/TD]
[TD]QQ[/TD]
[TD]ZZ[/TD]
[TD][/TD]
[TD]8[/TD]
[TD]-4[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]136[/TD]
[TD]GG[/TD]
[TD]RR[/TD]
[TD][/TD]
[TD]12[/TD]
[TD]-2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]137[/TD]
[TD]GG[/TD]
[TD]PP[/TD]
[TD][/TD]
[TD]12[/TD]
[TD]0[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]138[/TD]
[TD]OO[/TD]
[TD]ZZ[/TD]
[TD][/TD]
[TD]4[/TD]
[TD]-6[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]

If:
Column V = PP,
Column U <> GG

I want to SUM column X / 4.
+ ( SUM column Y / 2 ) * -1 )
/ Number of Unique values in Column Z

So, we should be looking in Row 134 (=PP, <>GG).
X (4/4) = 1
Y (-2/2)*-1) = 1
X + Y = 2
/ Z 1 Unique value
Therefore answer 2/1 = 2

I can get the X+Y = 2 using this formula:

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'; color: #000000}span.s1 {color: #006107}span.s2 {color: #ab30d6}span.s3 {color: #0057d6}span.s4 {color: #a54a29}</style>
=((SUMIFS(X134:X138,V134:V138,"PP",U134:U138,"<>GG")/4)+((SUMIFS(Y134:Y138,V134:V138,"PP",U134:U138,"<>GG")*-1)/2))

But when I divide it by the unique values, it gives me 4 (total of Column Z), as opposed to 1 (Total of column Z with the criteria =PP, <>GG). Therefore 2/4=0.5, not 2/1=2.

/SUM(IF(FREQUENCY(Z134:Z138,Z134:Z138)>0,1))

So there must be a way to either:
a) Include my IF criteria in the second half of the function so that it =1, or
b) State the IF criteria only once, so that it applies to the whole function. (To get the first part (X+Y) to work, I currently have to state it twice, as you can see.)

Anyone know how to piece it together?
Help much appreciated.

 

[pgc01]

Hi

Try dividing by:

/COUNT(1/FREQUENCY(IF(U134:U138<>"GG",IF(V134:V138="PP",Z134:Z138)),Z134:Z138))