SOLVED

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

Copper Contributor

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

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!

3 Replies
best response confirmed by MrStern (Copper Contributor)
Solution

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

That could be

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

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

@SergeiBaklanPerfect!  Thanks

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

@MrStern , you are welcome

1 best response

Accepted Solutions
best response confirmed by MrStern (Copper Contributor)
Solution

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

That could be

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