I have first column containing values in text and a second one containing duration for each value. I would like to merge two or more cells which has the same value in the first column and add the time values in the second column. How can I do this? Thank you in advance for any help.
2 Answers
This can be done easily with a pivot table. Just select your data and click Pivot Table
on the Insert ribbon.
After choosing a location for your pivot table (mine is at D1
on the same sheet in this sample, but you may find it better to have it on a separate sheet), just drag and drop the fields from the Pivot Table Field List into the appropriate categories: Event
will go under Row Values and Duration
will go under Values. Since Sum
is the default setting for pivot table values, this is all you need to do.
Use the formula =SubTotal(9, DURATION_RANGE)
for DURATION range and filter out the required text value(s). the Subtotal will show the sum of all the duration values of the selected texts.