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.

A B C D E Description Date Start Time End Time Total Duration Registration 28/08/2014 9:00:00 13:30:00 4:30:00 Registration 29/08/2015 10:00:00 12:00:00 2:00:00 Registration 29/08/2016 17:00:00 17:30:00 0:30:00 Search 28/08/2017 14:00:00 16:00:00 2:00:00 Search 28/08/2018 17:00:00 18:00:00 1:00:00 <tbody> </tbody>

<tbody>
</tbody>

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:

C N Description Duration Registration 7:00:00 Search Enquiry Convert External Registrations Card <tbody> </tbody> <tbody> </tbody>

<tbody>
</tbody>

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.