Countifs duplicate of one value - differentiating when second value is negative or positive

[MrStern]

Hi,

I am trying to create a count formula for the below scenario.

I am trying to count duplicates in one column, but only count them if they are either negative or positive values in a second column.

so as per image below, I am looking to count the Placement ID's (of which there are 4 ending in '312.

However I only want a result of 2 on each row as there are two Placements '312 that have a negative Local Amount, and there are two Placements that are positive Local Amount.

Any help would be appreciated.  I have tried all sorts of counts, and's if's etc with no luck for one reason or another.

Thanks!

[#2 · Accepted solution]

@MrStern 

That could be

=SUMPRODUCT(($C$3:$C$7=$C3)*(SIGN($D$3:$D$7)=SIGN($D3)))

[#3]

@Sergei BaklanPerfect!  Thanks

[#4]

@MrStern , you are welcome