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

americanpiggy

New Member
Joined
May 16, 2017
Messages
5
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 ---
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Forum statistics

Threads
1,223,907
Messages
6,175,300
Members
452,633
Latest member
DougMo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top