Filter and summarize

%3CLINGO-SUB%20id%3D%22lingo-sub-2102265%22%20slang%3D%22en-US%22%3EFilter%20and%20summarize%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2102265%22%20slang%3D%22en-US%22%3E%3CP%3EHi!%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20summarize%20a%20list%20of%20employees%20for%20calculating%20purposes%2C%20in%20which%20I%20want%20to%20keep%20some%20information%20in%20some%20columns%20but%20summarize%20or%20exclude%20data%20from%20another%20specific%20column.%20See%20below.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20first%20table%20show%20the%20data%20that%20I%20can%20export%20from%20the%20HR%20system.%20However%20some%20employees%20have%20more%20than%20one%20line%20due%20to%20either%20additional%20salary%20lines%20or%20an%20updated%20salary.%20My%20goal%20is%20to%20get%20a%20new%20sheet%20that%20will%20exclude%20double%20lines%20and%20summarize%20different%20salarytypes%20(see%20second%20table).%20Is%20this%20possible%20or%20do%20I%20have%20to%20do%20it%20with%20different%20formulas%20depending%20on%20which%20column%20I%20get%20data%20from%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20help!%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Ogbergo_0-1611759443786.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F249841i422226432011CEB8%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Ogbergo_0-1611759443786.png%22%20alt%3D%22Ogbergo_0-1611759443786.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2102265%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2102385%22%20slang%3D%22en-US%22%3ERe%3A%20Filter%20and%20summarize%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2102385%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F944707%22%20target%3D%22_blank%22%3E%40Ogbergo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20didn't%20put%20the%20results%20on%20a%20different%20sheet%2C%20although%20that%20could%20be%20easily%20done.%20I%20wanted%20to%20have%20the%20formulas%20visible%20on%20the%20same%20page%20as%20the%20raw%20data.....%20But%20here's%20a%20solution.%20There%20may%20be%20others.%20This%20does%20use%20the%20UNIQUE%20function%20which%20is%20only%20available%20on%20the%20newest%20versions%20of%20Excel.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi!

I am trying to summarize a list of employees for calculating purposes, in which I want to keep some information in some columns but summarize or exclude data from another specific column. See below.

 

The first table show the data that I can export from the HR system. However some employees have more than one line due to either additional salary lines or an updated salary. My goal is to get a new sheet that will exclude double lines and summarize different salarytypes (see second table). Is this possible or do I have to do it with different formulas depending on which column I get data from?

 

Please help!

 

Ogbergo_0-1611759443786.png

 

2 Replies

@Ogbergo 

 

I didn't put the results on a different sheet, although that could be easily done. I wanted to have the formulas visible on the same page as the raw data..... But here's a solution. There may be others.

 

I used the UNIQUE function (which is only available on the newest versions of Excel) to produce the list of employees on the secondary table; I also sorted them alphabetically, just to show how that could work. The sort could be done based on EE # also.

Then the SUMIFS function gets the numbers by name for any that are still valid (the assumption being that a date under "TO" means that number is no longer to be included.

 

 

@mathetes Ok, looks good. I'll try that. Thanks for you quick reply!