SOLVED

Summing data if date in another column is duplicated and removing unneccessary data

%3CLINGO-SUB%20id%3D%22lingo-sub-2626865%22%20slang%3D%22en-US%22%3ESumming%20data%20if%20date%20in%20another%20column%20is%20duplicated%20and%20removing%20unneccessary%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2626865%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20not%20sure%20I%20can%20clearly%20explain%20what%20I%20need%2C%20but%20I'll%20try%20my%20best.%20I've%20been%20struggling%20with%20it%20for%20a%20few%20days%20now%2C%20so%20I%20thought%20I%20might%20reach%20out%20for%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20two%20datasets%3A%3C%2FP%3E%3CP%3E1.%20Number%20of%20customers%20for%20every%20working%20day%20for%2030%20months%3CBR%20%2F%3E2.%20Number%20of%20calls%20and%20emails%20from%20customers%20for%20every%20day%20for%2030%20months%20-%20this%20data%20has%20weekends%20and%20bank%20holidays%20included%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20the%20data%20to%20be%20comparable%2C%20so%20I%20need%20Dataset%201.%20to%20stay%20as%20it%20is%20but%20in%20Dataset%202.%20I%20need%20to%3A%3CBR%20%2F%3E-%20change%20Saturdays%20and%20Sundays%20into%20Mondays%20(I've%20already%20done%20it.%20As%20I%20want%20to%20add%20customer%20queries%20from%20weekends%20to%20Mondays)%3CBR%20%2F%3E-%20Based%20on%20the%20dates%20in%20Column%20C%2C%20I%20need%20to%20add%20values%20in%20Columns%20D%20and%20E%3A%20Now%20I%20have%203%20Mondays%20each%20week%2C%20so%20when%20the%20same%20date%20appears%203%20times%20in%20Column%20C%2C%20I%20need%20it%20to%20appear%20only%20once%2C%20summing%20up%20the%20values%20in%20Columns%20D%20and%20E%2C%20so%20instead%20of%20having%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22205%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2277%22%3EDate%3C%2FTD%3E%3CTD%20width%3D%2264%22%3ECalls%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EEmails%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E07%2F01%2F2019%3C%2FTD%3E%3CTD%3E8%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E07%2F01%2F2019%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E07%2F01%2F2019%3C%2FTD%3E%3CTD%3E25%3C%2FTD%3E%3CTD%3E6%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22205%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2277%22%3EDate%3C%2FTD%3E%3CTD%20width%3D%2264%22%3ECalls%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EEmails%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E07%2F01%2F2019%3C%2FTD%3E%3CTD%3E35%3C%2FTD%3E%3CTD%3E9%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20was%20trying%20to%20figure%20out%20how%20to%20do%20that%2C%20I%20tried%20to%20use%20the%20SUMIF%20function%2C%20but%20I'm%20not%20sure%20it's%20the%20correct%20approach.%20Perhaps%20someone%20would%20be%20able%20to%20help%20as%20the%20idea%20of%20making%20it%20manually%20makes%20me%20feel%20like%20a%20fool.%3CBR%20%2F%3E%3CBR%20%2F%3EI'm%20attaching%20a%20sample%20file%20to%20make%20it%20a%20little%20bit%20more%20clear.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2626865%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2626899%22%20slang%3D%22en-US%22%3ERe%3A%20Summing%20data%20if%20date%20in%20another%20column%20is%20duplicated%20and%20removing%20unneccessary%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2626899%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1123773%22%20target%3D%22_blank%22%3E%40Roksana%3C%2FA%3E%26nbsp%3BConsider%20using%20a%20Pivot%20Table%20as%20demonstrated%20in%20the%20attached%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2626916%22%20slang%3D%22en-US%22%3ERe%3A%20Summing%20data%20if%20date%20in%20another%20column%20is%20duplicated%20and%20removing%20unneccessary%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2626916%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%3CBR%20%2F%3EThat's%20brilliant!%20I%20haven't%20thought%20about%20it%2C%20thank%20you%20so%20much!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2626953%22%20slang%3D%22en-US%22%3ERe%3A%20Summing%20data%20if%20date%20in%20another%20column%20is%20duplicated%20and%20removing%20unneccessary%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2626953%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1123773%22%20target%3D%22_blank%22%3E%40Roksana%3C%2FA%3E%26nbsp%3BOoops!%20just%20realized%20that%20the%20dates%20are%20not%20synchronized%20between%20customers%20on%20one%20side%20and%20Calls%20and%20Emails%20on%20the%20other.%20Better%20to%20create%20a%20table%20with%20one%20Date%20column%20and%20then%20summarize%20with%20a%20pivot%20table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EEdit%3C%2FSTRONG%3E%3A%20Alternatively%2C%20use%20Power%20Pivot%20or%20Power%20Query.%20Attached%20a%20file%20that%20contains%20three%20different%20solutions%2C%20all%20leading%20to%20the%20same%20end%20result.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello,

 

I am not sure I can clearly explain what I need, but I'll try my best. I've been struggling with it for a few days now, so I thought I might reach out for help.

 

I have two datasets:

1. Number of customers for every working day for 30 months
2. Number of calls and emails from customers for every day for 30 months - this data has weekends and bank holidays included

 

I need the data to be comparable, so I need Dataset 1. to stay as it is but in Dataset 2. I need to:
- change Saturdays and Sundays into Mondays (I've already done it. As I want to add customer queries from weekends to Mondays)
- Based on the dates in Column C, I need to add values in Columns D and E: Now I have 3 Mondays each week, so when the same date appears 3 times in Column C, I need it to appear only once, summing up the values in Columns D and E, so instead of having this:

 

DateCallsEmails
07/01/201982
07/01/201921
07/01/2019256

 

I need this:

 

DateCallsEmails
07/01/2019359

 

I was trying to figure out how to do that, I tried to use the SUMIF function, but I'm not sure it's the correct approach. Perhaps someone would be able to help as the idea of making it manually makes me feel like a fool.

I'm attaching a sample file to make it a little bit more clear.

 

 

4 Replies

@Roksana Consider using a Pivot Table as demonstrated in the attached file.

Screenshot 2021-08-09 at 12.19.47.png

@Riny_van_Eekelen
That's brilliant! I haven't thought about it, thank you so much!
best response confirmed by Roksana (New Contributor)
Solution

@Roksana Ooops! just realized that the dates are not synchronized between customers on one side and Calls and Emails on the other. Better to create a table with one Date column and then summarize with a pivot table.

 

Edit: Alternatively, use Power Pivot or Power Query. Attached a file that contains three different solutions, all leading to the same end result.

@Riny_van_Eekelen 

 

Yeah, I've only included one date column in my table, and it worked perfectly. Thanks again!