excel help

%3CLINGO-SUB%20id%3D%22lingo-sub-3302197%22%20slang%3D%22en-US%22%3Eexcel%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3302197%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECould%20anyone%20help%20me%20with%20following%20tasks%20using%20just%20one%20excel%20formula%3F%20(dataset%20below)%3C%2FP%3E%3CP%3E%26nbsp%3B%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%22data.PNG%22%20style%3D%22width%3A%20493px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F369064i66E6E6E9E2656416%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22data.PNG%22%20alt%3D%22data.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E1.Percentage%20of%20quarter's%20sales%20made%20by%20each%20trader.%26nbsp%3B%20(for%20instance%20David%C2%B4s%20sales%20for%20Q1..)%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BDavid%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BJames%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BSarah%3C%2FP%3E%3CP%3EQ1%3C%2FP%3E%3CP%3EQ2%3C%2FP%3E%3CP%3EQ3%3C%2FP%3E%3CP%3EQ4%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E2.%26nbsp%3B10.%20Who%20had%20the%20first%2C%20second%20and%20third%20highest%20sales%20by%20names%3F%3C%2FP%3E%3CTABLE%20width%3D%22740%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22740%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E3.%26nbsp%3B.%20What%20percentage%20is%20each%20sale%20for%20all%20those%20made%20in%20the%20same%20year%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20any%20help%3C%2FP%3E%3CP%3ETom%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3302197%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-3313156%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3313156%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eand%20answer%20for%20this%20task%20should%20be%20name%20of%20person.%20So%20who%20has%20highest%2C%20second%20highest%20and%20third%20highers%20sales.%20Any%20ideas%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3302430%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3302430%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1380625%22%20target%3D%22_blank%22%3E%40tomas395%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20can%20use%20SUMIF%20or%20SUMIFS%20to%20sum%20the%20sales%20per%20person.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3302391%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3302391%22%20slang%3D%22en-US%22%3EYes%20but%20there%20is%20problem%20%2C%20how%20to%20sum%20sales%20for%20each%20person%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3302343%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3302343%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1380625%22%20target%3D%22_blank%22%3E%40tomas395%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECheck%20out%20the%20RANK%20function.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3302300%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3302300%22%20slang%3D%22en-US%22%3EThanks%20I%20got%20it.%3CBR%20%2F%3E%3CBR%20%2F%3EDo%20you%20have%20any%20hints%20for%202.%20tasks%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3302248%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3302248%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1380625%22%20target%3D%22_blank%22%3E%40tomas395%3C%2FA%3E%20You%20need%20to%20specify%20the%20date%20range%20twice%20in%20the%20SUMIFS%20formula.%20For%20example%2C%20Q1%20of%202013%20is%20from%20January%201%2C%202013%20to%20March%2031%2C%202013.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DSUMIFS(date_range%2C%20%22%26gt%3B%3D%22%26amp%3BDATE(2013%2C1%2C1)%2C%20date_range%2C%20%22%26lt%3B%3D%22%26amp%3BDATE(2013%2C3%2C31))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3302207%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3302207%22%20slang%3D%22en-US%22%3Ebut%20how%20to%20sum%20quarters%20since%20there%20is%20just%20date%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3302202%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3302202%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1380625%22%20target%3D%22_blank%22%3E%40tomas395%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESince%20this%20is%20homework%2C%20I'll%20just%20provide%20a%20hint%3A%20you%20can%20use%20the%20%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fsumifs-function-c9e748f5-7ea7-455d-9406-611cebce642b%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3ESUMIFS%20function%3C%2FA%3E%20to%20sum%20the%20sales%20of%20a%20specific%20trader%20in%20a%20specific%20quarter%2C%20and%20also%20to%20sum%20all%20sales%20in%20the%20same%20quarter.%20The%20quotient%20of%20the%20two%20sums%20will%20return%20the%20percentage.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3313850%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3313850%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1380625%22%20target%3D%22_blank%22%3E%40tomas395%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20can%20create%20a%20list%20of%20persons%20and%20sales%20and%20sort%20it%20descending%20on%20sales.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3313988%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3313988%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%20%26nbsp%3BI%20do%20not%20think%20it%20will%20work%20out%2C%20I%20need%20just%20one%20formula(see%20below)%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22problem.PNG%22%20style%3D%22width%3A%20656px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F369266i87828B1EF5C70DC5%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22problem.PNG%22%20alt%3D%22problem.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3314285%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3314285%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1380625%22%20target%3D%22_blank%22%3E%40tomas395%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou'll%20need%20to%20use%20a%20combination%20of%20INDEX%20and%20MATCH.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3317137%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3317137%22%20slang%3D%22en-US%22%3ECould%20you%20please%20clarify%20it%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3317178%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3317178%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1380625%22%20target%3D%22_blank%22%3E%40tomas395%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20think%20it's%20time%20you%20did%20some%20research%20yourself.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3317227%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3317227%22%20slang%3D%22en-US%22%3EDo%20not%20worry%20that%20is%20what%20I%20have%20been%20doing%20for%20the%20last%20few%20hours%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3317283%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3317283%22%20slang%3D%22en-US%22%3Ethat%20is%20what%20I%20have%20been%20doing%20for%20the%20last%20couple%20of%20hours%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi,

 

Could anyone help me with following tasks using just one excel formula? (dataset below)

 

 

data.PNG

1.Percentage of quarter's sales made by each trader.  (for instance David´s sales for Q1..)

           David         James           Sarah

Q1

Q2

Q3

Q4

 

2. 10. Who had the first, second and third highest sales by names?

 

3. . What percentage is each sale for all those made in the same year?

 

Thank you for any help

Tom

15 Replies

@tomas395 

Since this is homework, I'll just provide a hint: you can use the SUMIFS function to sum the sales of a specific trader in a specific quarter, and also to sum all sales in the same quarter. The quotient of the two sums will return the percentage.

but how to sum quarters since there is just date

@tomas395 You need to specify the date range twice in the SUMIFS formula. For example, Q1 of 2013 is from January 1, 2013 to March 31, 2013.

 

=SUMIFS(date_range, ">="&DATE(2013,1,1), date_range, "<="&DATE(2013,3,31))

Thanks I got it.

Do you have any hints for 2. tasks?

@tomas395 

Check out the RANK function.

Yes but there is problem , how to sum sales for each person

@tomas395 

You can use SUMIF or SUMIFS to sum the sales per person.

@Hans Vogelaar 

 

and answer for this task should be name of person. So who has highest, second highest and third highers sales. Any ideas?

@tomas395 

You can create a list of persons and sales and sort it descending on sales.

@Hans Vogelaar   I do not think it will work out, I need just one formula(see below)problem.PNG

@tomas395 

You'll need to use a combination of INDEX and MATCH.

Could you please clarify it?

@tomas395 

I think it's time you did some research yourself.

Do not worry that is what I have been doing for the last few hours
that is what I have been doing for the last couple of hours