=if(sumproduct( or if(countifs(

[srizki]

I have Cost centers in column G and interest in column Y, Cost centers are repeated and so interest rates accordingly, only one interest rate should be for one cost center, so if there are more than one rates there is an error, so for example

[TABLE="width: 169"]
<tbody>[TR]
[TD]411044924
[/TD]
[TD]5.280613
[/TD]
[/TR]
[TR]
[TD]411044924
[/TD]
[TD]5.280613
[/TD]
[/TR]
[TR]
[TD]411044924
[/TD]
[TD]5.280613
[/TD]
[/TR]
[TR]
[TD]411044924
[/TD]
[TD]5.280613
[/TD]
[/TR]
[TR]
[TD]411044924
[/TD]
[TD]5.280613
[/TD]
[/TR]
[TR]
[TD]411044924
[/TD]
[TD]5.280613
[/TD]
[/TR]
[TR]
[TD]411044978
[/TD]
[TD]10.561225
[/TD]
[/TR]
[TR]
[TD]411044978
[/TD]
[TD]10.561225
[/TD]
[/TR]
[TR]
[TD]411044978
[/TD]
[TD]10.561225
[/TD]
[/TR]
[TR]
[TD]411044978
[/TD]
[TD]10.561225
[/TD]
[/TR]
[TR]
[TD]411044976
[/TD]
[TD]10.561225
[/TD]
[/TR]
[TR]
[TD]411044976
[/TD]
[TD]10.561225
[/TD]
[/TR]
[TR]
[TD]411044976
[/TD]
[TD]10.561225
[/TD]
[/TR]
[TR]
[TD]411044976
[/TD]
[TD]10.561225
[/TD]
[/TR]
[TR]
[TD]411044919
[/TD]
[TD]5.071967
[/TD]
[/TR]
[TR]
[TD]411044919
[/TD]
[TD]5.071967
[/TD]
[/TR]
</tbody>[/TABLE]

I need a formula that should indicate if the interest rate is more than one of any of these cost centers.
I am using
=IF(SUMPRODUCT($G$80:G80=G80)*($Y$80:Y80=Y80)>1,"ERROR","OKAY")


Best regards,

Sohail Rizki

 

[BiocideJ]

If I am correctly understanding your request, this should be the formula you are looking for:

=IF(COUNTIFS($G:$G,$G2,$Y:$Y,"<>"&$Y2)>0,"ERROR","OKAY")

typed in row 2 and dragged down for each row.

 

[srizki]

Yes,
this is working, great,

Thanks