adding up 2 rows if the id and name match in a third column.

not sure I said that right.
I have an odd problem and maybe it has to be taken in steps or can't be done in excel.
I enter time based on projects in excel.
each has a unique project name, but the id may be the same as another project.
the people that use these sheets need it to show one row when that happens, but I still need it split out to show the unique name and id hours split up.

I was hoping that I could force it to check each line and write it in another sheet, and if it matched, add it instead of writing a new row.

I have santized the sheet so I could upload it as is. just different names and project names and id's.

person 6 is a good example. multiple projects, but 2 have the same project id.
is there a way to copy this information into another sheet for each person but add up any duplicate rows?

I tried to do this in stages.
copy over automatically everything from sheet2 to sheet 3 but lable any dupe id's.
then I thought I would check if it was marked dupe before and add it to the previous line marked dupe.
but I can't figure out how to check 2 different things on the same row against 2 different things on another row. I keep getting stuff that cross checks but doesn't check on the same line.


a pivot table doesn't work on this sheet for some reason. I inherited the sheet, and have just been fixing things on it instead of trying to redo the whole thing.


worst case, I have been copying and pasting and adding it all up manually.

Andrewsh,

Welcome to the forum. (And fair play to you for persevering with that sheet!) As you've been copying and pasting etc, could you do a little more, and show us (in your file) the expected outcome for eg Person 6?

That should be do-able. I need to log-off now, but will come back to this tomorrow.

Sorry Andrew, I forgot all about this.

I've got you most of the way - using the combination of project ID & Resource Name, you can use a SUMIFS function to SUM a range IF multiple criteria(S) are met. So, using the formula:

=SUMIFS(Sheet2!AJ:AJ,Sheet2!$A:$A,TOTALS!$A$3,Sheet2!$C:$C,TOTALS!$B$3)

in F3 on the TOTALS sheet, Excel is looking for the SUM of entries in Column AJ on Sheet 2, IF it finds a cell in Column A of Sheet 2 where the cell contents are equal to Cell A3 on the TOTALS sheet, and IF it finds a cell in Column C of Sheet 2 where the contents are equal to Cell B3 on the TOTALS sheet - both of these matches must be found in the same row for the condition to be true.

However, there is something strange going on with your data. I've checked multiple times, and rows where the project ID = 14n are being ignored. I can't figure out why, so I suggest you throw this out to the forum at large, as I'm out of time here. I've no doubt that other members will be able to spot what it is that I'm overlooking.

Hope this helps.

after a quick internet search, I found I should not use imsub.
fixing that, makes your formula work.

While auto populating the whole 3rd sheet would be great names and projects, this is still way faster than I had it.
Thanks BB1972.