How to get total sum of specific data in Excel

Help
2

Hi,

There are various ways to do this; some are more efficient than others and some are more difficult to understand.

One way to solve this we can use a .net line of code that uses the .GroupBy() function. This generates a datatable instantly with all the values you want: CallerId, Count, and Sum

Here is a link to that method which you can look over and try it out:

you would need to make some adjustments for your dataset, but it does have a place for the CallerID and Sum

The second method is another .net method but you will need to loop over the unique values in column 1, then match up the rows for each CallerID and take the count and sum of each. Then, Add the data to another data table.

Psuedocode for this might look like this:

Build Data Table => Columns: CallerID, Destination Count, Total Duration

For each callerID In callerDataset.AsEnumerable.Select(Function(r) r("CallerID").ToString.Trim).ToArray.Distinct
    Assign activity: callerIDRows = callerDataset.AsEnumerable.Where(Function(r) r("CallerID").ToString.Trim = callerID).ToArray
    Assign activity: callerCount = callerIDRows.Count
    Assign activity: callerTotalDuration = (new DateTime() + (new TimeSpan(callerIDRows.Sum(Function(r) DateTime.ParseExact(r("Duration").ToString.Trim,"m:ss",Globalization.CultureInfo.CurrentCulture).Ticks ))) ).ToString("m:ss")

    Add Data Row, ArrayRow: {callerID, callerCount.ToString, callerTotalDuration}

Also, refer to how to sum the duration, cause it involves some conversions to add Ticks, then use the timespan to format to the “m:ss” format (which can be changed to other format if desired).

The other method I will mention is to not use .net coding, which means that you run each row through a loop for each callerID, and sum each item during the loop. This can actually make it more complicated than it needs to be and is slower, but is less coding. However, you will need some coding to sum the durations, as shown in the previous method mentioned.

If you have any trouble solving this with the above methods, let me know and I’ll try to assist.

Regards.

2 Likes