CODEWITHSUNDEEP
excelvba
Amir
Amir

Reputation: 1979

how to group a data in excel and calculate the sum of group data

I want to implement some code in excel macro to group some data for me and then calculate the sum of grouped column. please look at below data.

I want to check instead of writing macro is there any way to implement it via excel formula? I mean via excel formula I group them and calculate the total time in another column?

Fix.No  Duration        Expected result 
111 00:00:55        Fix.No  Total Duration
111 00:59:15        111 01:02:25
123 00:10:55        123 00:10:55
111 00:01:15        265 00:00:45
265 00:00:45        214 01:00:05
214 01:00:05

Upvotes: 0

Views: 8915

Answers (2)

user2905919
user2905919

Reputation: 43

To get data similar to how SQL gives the output can be done in excel using the following steps

  1. Select the source data in pivot
  2. Arrange all the non aggregate columns in row filter in pivot
  3. Select the aggregate columns in values in pivot
  4. Pivottable options--->Design----->Report Layout------>Show in tabular form
  5. Then select Repeat all item labels from Report layout tab
  6. Remove all the Grand Total and Sub Total Hope this helps and makes modifications accordingly

Upvotes: 1

kad81
kad81

Reputation: 10950

If you're willing to manually (or via macro) enter the first column values (each distinct value as you have it there), then you can just use the SUMIF function to calculate the totals.

Upvotes: 0

Related Questions