# 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

##### New Member
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?

Last edited:

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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
ABC
1DescriptionTotal
2Patient Registration7:00
3Patient Search3:00
4Patient Enquiry0:00
5Convert External Registrations0:00
6Hospital Card0:00
Sheet2
Excel Workbook
ABCDE
1DescriptionDateStart TimeEnd TimeTotal Duration
2Patient Registration28/08/201409:00:0013:30:0004:30:00
3Patient Registration29/08/201510:00:0012:00:0002:00:00
4Patient Registration29/08/201617:00:0017:30:0000:30:00
5Patient Search28/08/201714:00:0016:00:0002:00:00
6Patient Search28/08/201817:00:0018:00:0001:00:00
Sheet1

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)

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.

Thanks a lot. That works fine.

Replies
11
Views
385
Replies
15
Views
384
Replies
3
Views
128
Replies
4
Views
341
Replies
0
Views
140

1,203,061
Messages
6,053,308
Members
444,651
Latest member
markkuznetsov1

### 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.

### Which adblocker are you using?

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

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