Summing column of values and including duplicates only once (with conditions).

[lburbach2]

I would like to sum a column of values, but only include one value if it comes from the same "date" and "time" column. In my problem, values are always duplicated when they have the same date and time. They may also be randomly duplicated with DIFFERENT date and times (this is okay, I would like to sum both in this case).

I would like to sum cells, C:2+C:3+C:5+C:6+C:7+C:9, (not C:4 or C:8 since they have the same date and time).

 

[sandy666]

with Power Query

// CalculatedSum
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Distinct = Table.Distinct(Source, {"Date", "Time"}),
    CSum = List.Sum(Distinct[Values])
in
    CSum

Date	Time	Values		CalculatedSum
4/1/2020	4:21PM	2		21
4/1/2020	4:27 PM	4
4/1/2020	4:27 PM	4
4/1/2020	8:36 AM	6
4/1/2020	7:08 PM	3
4/1/2020	10:12 PM	2
4/1/2020	10:12 PM	2
4/2/2020	1:08 AM	4

PowerQuery add-in for XL 2010/2013, all above has PQ built-in

 

[lburbach2]

Thank you for the response, but I do not have the option to install the Power Query command for my excel. I was wondering if there was any way to do it using a formula.

 

[sandy666]

sure, maybe someone else
have a nice day

 

[CA_Punit]

I was not able to find a formula but I have a method through remove duplicate option,

Steps:-
1. Connvert Date and Time to Number format.
2. Concatenate Date, Time and Amount ( =A4&B4&C4) Using Ampersand)
3. Go to Home and search for Duplicates (Under Conditional formating)
4. Remove Duplicates under Data Tab.
5. Sum all the Data.