Help to only count value once in RANGE

[D_Spark]

Hi all,

Can someone please assist me?
On a worksheet I have the following formula

{=SUM((V!$J$2:$J$5000=B5)*(V!$D$2:$D$5000=21))}

I wish to expand this formula so that each value in range (V!$A$2:$A$5000) matching the above criteria is on counted once

 

[D_Spark]

Hi all,

Can someone please assist me?
On a worksheet I have the following formula

{=SUM((V!$J$2:$J$5000=B5)*(V!$D$2:$D$5000=21))}

I wish to expand this formula so that each value in range (V!$A$2:$A$5000) matching the above criteria is only counted once[/QUOTE]

 

[barry houdini]

Try this formula

=SUM(IF(FREQUENCY(IF((V!$J$2:$J$5000=B5)*(V!$D$2:$D$5000=21)*(V!$A$2:$A$5000<>""),MATCH(V!$A$2:$A$5000,V!$A$2:$A$5000,0)),ROW(V!$A$2:$A$5000)-ROW(V!$A$2)+1),1))

confirmed with CTRL+SHIFT+ENTER

 

[D_Spark]

Try this formula

=SUM(IF(FREQUENCY(IF((V!$J$2:$J$5000=B5)*(V!$D$2:$D$5000=21)*(V!$A$2:$A$5000<>""),MATCH(V!$A$2:$A$5000,V!$A$2:$A$5000,0)),ROW(V!$A$2:$A$5000)-ROW(V!$A$2)+1),1))

confirmed with CTRL+SHIFT+ENTER

Houdini by name, Houdini by nature: Your suggested worked like a treat, Thanks