Sum of one column based on unique values of another column and show the value in a different worksheet that has the unique value

[Vinosh]

Hi,

In one worksheet, I have the following data.
[TABLE="width: 483"]
<tbody>[TR]
[TD][TABLE="width: 483"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]Description[/TD]
[TD]Date[/TD]
[TD]Start Time[/TD]
[TD]End Time[/TD]
[TD]Total Duration[/TD]
[/TR]
[TR]
[TD]Registration[/TD]
[TD]28/08/2014[/TD]
[TD="align: right"]9:00:00[/TD]
[TD="align: right"]13:30:00[/TD]
[TD="align: right"]4:30:00[/TD]
[/TR]
[TR]
[TD]Registration[/TD]
[TD]29/08/2015[/TD]
[TD="align: right"]10:00:00[/TD]
[TD="align: right"]12:00:00[/TD]
[TD="align: right"]2:00:00[/TD]
[/TR]
[TR]
[TD]Registration[/TD]
[TD]29/08/2016[/TD]
[TD="align: right"]17:00:00[/TD]
[TD="align: right"]17:30:00[/TD]
[TD="align: right"]0:30:00[/TD]
[/TR]
[TR]
[TD]Search[/TD]
[TD]28/08/2017[/TD]
[TD="align: right"]14:00:00[/TD]
[TD="align: right"]16:00:00[/TD]
[TD="align: right"]2:00:00[/TD]
[/TR]
[TR]
[TD]Search[/TD]
[TD]28/08/2018[/TD]
[TD="align: right"]17:00:00[/TD]
[TD="align: right"]18:00:00[/TD]
[TD="align: right"]1:00:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

In another worksheet, where the unique value in column A is given, I need to get the sum of values in column E for unique values in column A, i.e. Registration = 7:00:00, like:

[TABLE="width: 348"]
<tbody>[TR]
[TD][TABLE="width: 348"]
<tbody>[TR]
[TD][TABLE="width: 348"]
<tbody>[TR]
[TD]C[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]Description[/TD]
[TD]Duration[/TD]
[/TR]
[TR]
[TD]Registration[/TD]
[TD]7:00:00[/TD]
[/TR]
[TR]
[TD]Search[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Enquiry[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Convert External Registrations[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Card[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

What is the formula to match the unique values in the same sheet, match it with another sheet and fetch the sum of unique values?

 

[FormR]

What is the formula to match the unique values in the same sheet, match it with another sheet and fetch the sum of unique values?

Hi, welcome to MrExcel.

It sounds like SUMIF() - see example below:

Excel Workbook
	A	B	C
1	Description	Total
2	Patient Registration	7:00
3	Patient Search	3:00
4	Patient Enquiry	0:00
5	Convert External Registrations	0:00
6	Hospital Card	0:00
Sheet2

Excel Workbook
	A	B	C	D	E
1	Description	Date	Start Time	End Time	Total Duration
2	Patient Registration	28/08/2014	09:00:00	13:30:00	04:30:00
3	Patient Registration	29/08/2015	10:00:00	12:00:00	02:00:00
4	Patient Registration	29/08/2016	17:00:00	17:30:00	00:30:00
5	Patient Search	28/08/2017	14:00:00	16:00:00	02:00:00
6	Patient Search	28/08/2018	17:00:00	18:00:00	01:00:00
Sheet1

 

[Vinosh]

Hi, Thanks for the quick reply.

I get 0:00:00 instead of the actual number of hours, while doing this way : =SUMIF(Sheet1!A:A,A2,Sheet1!E:E)

 

[FormR]

You need to check 2 things:

1. That the value in A2 exactly matches the values in column A of sheet1 - you can test this with =Sheet1!A2=A2
2. That the values in column E of sheet1 are numeric - you can test this with =ISNUMBER(Sheet1!E2)

Change the 2 to one of the rows that should be summing - both formula should return TRUE.

 

[Vinosh]

Thanks a lot. That works fine.

 

[FormR]

Thanks a lot. That works fine.

Great, thanks for letting us know.