How to sum up unique cell values based on a condition?

Question

In D10 I want to sum ColumnD values (in rows 2:9, derived with a VLOOOKP formula) but each only one time for each distinct ColA value:

Col A   Col B   Col C   Col D
Test2   testlast2   ttttt   1         *
Test2   testlast2   ttttt   1
Test4   testlast4   ttttt   1         *
Test5   testlast5   ttttt   1         *
Test6   testlast6   ttttt   -BLANK-
Test7   testlast7   ttttt   -BLANK-
Test7   testlast7   ttttt   1         *
Test7   testlast7   ttttt   1

In the example above the result should be 4 as indicated by asterisks.

I have tried formulae like:

=SUMPRODUCT((A2:A9<>"")/COUNTIF(A2:A9,A2:A9&"")) 

(which returns 5) without success.

Answer 1

Try this array formula:

=SUMPRODUCT(IFERROR((D2:D9<>"")/COUNTIFS(D2:D9,"<>",A2:A9,A2:A9&""),0))

Array formulas require Ctrl+Shift+Enter rather than simply Enter.

---

Here is another standard formula version that will never allow a #DIV/0! to occur so no need for IFERROR:

=SUMPRODUCT((D2:D9<>"")/(COUNTIFS(D2:D9,"<>",A2:A9,A2:A9&"")+(D2:D9="")))

Answer 2

Maybe:

=SUMPRODUCT(D2:D9,E2:E9)  

with, in E2 copied down to E9:

=1*(COUNTIF(A2:A9,A2)=1)