Sum only the first instance of a number tied to an ID, and then only sum those that tie to a label criteria

[americanpiggy]

Hello and thanks to anyone who can help! I am trying to:

1. In cell B2, Look at column A7:A23, and only sum numbers in D7:D23 if they match what is in A2
2. But, I *only want the first instance* of the numerical value per project ID in D7:D23, because otherwise, I double count. For example, project 36 is only $2000, not $6000. The task ID though is what shows and needs to be shown, but forces.

So far, I have come up with:
=(SUMPRODUCT((B24:B40<>"")/COUNTIF(B24:B40,B24:B40&""),D24:D40))

The above formula, so far, sums the first cost value in column D based on the project ID in column B.
Again, this allows me to not double count COSTS in column D based on the Special-ID in column B.

Now, how do I add in logic that allows me to say "only do this for the criteria in A2 that matches in column A range.


-- removed inline image ---

 

[Aladin Akyurek]

Thank you for your help Aladin - the formula works perfectly! Good to know about COUNTIF(S) not being too efficient too.

Glad to help. Thanks for the update.