Data Set in Excel

Good afternoon all. I have a spreadsheet with data for several sports and the number of players enrolled in various camps. What I need to do is be able to have a totals section at the bottom summarizing the unique sports in the data set and total number of players enrolled for each unique sport. The screen shot outlines best what I'm dealing with. I tried to figure this out by doing some research on the Internet and in Excel help but, I haven't had any success.

Any help would be appreciated! Thanks so much!

✔ Best Answer

I started with a sheet that looks exactly like your Screen Capture.

Sportsin A1, your list of Sports in A2:A10,Playersin B1, your number values in B2:B10Totalsin A12I then copied the formula used in the downloaded spreadsheet into A13 and modified it to match your ranges:

=IF(SUMPRODUCT((A2:A10<>"")*ISERROR(MATCH(A2:A10,$A$12:A12,0)))<>0,INDEX(A2:A20,MATCH(TRUE,ISERROR(IF(ISBLANK(A2:A10),FALSE,MATCH(A2:A10,$A$12:$A12,0))),0),1),"")

I entered it using Ctrl-Shift-Enter since it is an array formula. This added the brackets { } around it.

I dragged it down to A18 to allow room for 2 new sports and got this:

A 12 Totals 13 Hockey 14 Baseball 15 Football 16 Soccer 17 18I modified Mike's SUMIF formula to account for blank cells in Column A and entered this in B13, then dragged it down to B18.

=IF(A13="","",SUMIF($A$2:$A$10,A13,$B$2:$B$10))

I now have this:

A B 12 Totals 13 Hockey 575 14 Baseball 48 15 Football 225 16 Soccer 290 17 18I then replaced Hockey in A9 with Ultimate and ended up with this:

A B 12 Totals 13 Hockey 395 14 Baseball 48 15 Football 225 16 Soccer 290 17 Ultimate 180 18There you go...A live list that updates with changes to your main list for both the Sport names and the Player values.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Looks like you need the =SUMIF() function, something like: =SUMIF(A2:A10,"Hockey",B2:B10)

We used the text string Hockey, so you need to change this string for your other sports.

or

=SUMIF($A$2:$A$10,A13,$B$2:$B$10)

We used the cell containing the text string, so you need only drag the formula down three more cells and you should have what your looking for.

See how that works for you.

MIKE

Mike, Thanks very much. That works for the calculation of the total number of players. But, what formula would actually figure out the unique sports and place them in the list under Totals? For example, I listed the 4 unique sports under Totals but, I need a formula that would figure this out for me.

Again, any help would be awesome. Thanks.

Steve

Using the example you posted, put the formula: =SUMIF($A$2:$A$10,A13,$B$2:$B$10)

In cell B13 of your example and drag down three cells, you should get the totals for each sport.

Is that what you want, or am I missing something?

MIKE

I did that and it works great. But, the sports listed in the Totals area I typed in. But, imagine I have 10 unique sports listed in the data but, 100 rows of data with multiple occurrences of each sport. I want a formula in the Totals section that will figure out the unique sports listed in the data and list them in the Totals section. So, in A13 where it currently has Hockey a formula would figure that out for me. Something that would analyze the data in A2:A10 and then list each unique sport without multiples. I hope I explained it better this time.

You need to do some Advance Filtering. This is for 2007, your using 2003, so you may need to hunt for the correct buttons.

As an example, place your cursor in cell D13.

Select the Data Tab

Select the Advance button under Sort & FilterA small pop up window should appear.

Look for the

Action:button and change it to:Copy to Another LocationIn the

List Range:box enter the range: $A$2:$A$10Leave the

Critera Range:box blankIn the

Copy To:box enter you current cell location D13Here is the important part, make sure you Check the box next

toUnique Records OnlyClick OK and you should have your list of unique sports.

See how that works.

MIKE

Just a quick note: This filtering is CASE Sensitive,

So Hockey is not the same as hockey.

MIKE

Sorry, I don't do this enough to remember all the tricks. If you having problems with the filtering,

do a sort of your original lists first.Select column A2:B10 and then Sort

Now when you do the Advance Filtering you should get only the unique values.

MIKE

A couple of comments on Advanced Filtering.... 1 - AFAIK, you do not need to sort the original data to get a list of unique values.

2 - AFAIK, Advance Filtering is not case sensitive. e.g. with a list like this...

Softball hockey softball HockeyYou will get...

Softball hockeyAF should return the first occurrence of each string, retaining the case of that occurrence, but ignoring all other occurrences, regardless of the case.

3 - You may not need this info now, but it's something to keep it in your back pocket:

When you use "Filter to another location", you can't

pushthe filtered list to another sheet, but you canpullit to another sheet. If you start the AF on Sheet2, you can reference a List Range on Sheet1 and use a Copy To location on Sheet2.In other words, the location of the

filteredlist must be the active sheet, but theunfiltereddata can be on any sheet.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

The only way I could get it to filter correctly was by sorting the data first. When I filter your list:

A 19) Softball 20) hockey 21) softball 22) HockeyI get:

B 19) Softball 20) hockey 21) softballNot sure what's going on.

Steps:

With cell B19 selected.

On the Ribbon, Select Data Tab

Click Advanced

Click Button, Copy to Another Location

Select Range of cells: $A$19:$A$22

Skip Criteria box

Select Copy to: Sheet1!$B$19

Check Box: Unique Records Only

Click OKWhat am I missing?

MIKE

Sorry, I wasn't very clear. I didn't include the Label in my list. If you select A19:A22

A 19) Softball 20) hockey 21) softball 22) Hockey...the filter will assume A19 is a label and not data and return exactly what you posted.

B 19) Softball 20) hockey 21) softballB19 is a Label. (Select B19 and look in the Name box.)

Change the List to this, and filter it:

A 19 Games 20 Hockey 21 hockey 22 Football 23 football 24 acrobatics 25 AcrobaticsYou should get this:

B 19 Games 20 Hockey 21 Football 23 acrobaticsYou'll note that the data was not sorted.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Now that you explain it, I remember you need a Label, it's not something that is obvious at first glance.

But oddly enough, it is part of the error message you get if you do it wrong.Like I said, I don't use this function very often.

So now we change the instructions for the OP to:

In the List Range: box enter the range:

$A$1:$A$10 <Must Include Labelbut, unfortunately this also copies the Label.

So we either just delete the label row

or

instead of doing aCopy to Another Location,

do aFilter the List In-Place.The Filter in Place does NOT delete any info, it simply hides it.

After the Filter in Place

then Copy just the sports

Paste to location you wantFinally, Click

Clearbutton in the Sort & Filter section to un-hide the data.How does that sound?

MIKE

Why wouldn't the OP want to use the label? His screencast has a label of

Sportsin Column A.Use the Sports label above the unique list of sports and Totals or Players above the SUMIF formulas.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Use the Sports label above the unique list of sports and Totals or Players above the SUMIF formulas.Sounds like a winner to me.

MIKE

Let's hope that the OP likes the combination of our 2 suggestions. Teamwork!

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

I have to thank both of you big time! This really helped and it worked. Although, ideally I was looking for a solution whereby the "filtering" would be real time. In other words, if a sport was changed from Football to say Wrestling then the unique list below would automatically update. Does that take some macro or VB programming or is there a feature built into Excel that can do this? If that cannot be done then what you both have provided me is great and essentially solves my problem. My additional issue is how do I select a "Set Best Answer" when you both have been great? Can I set two best answers? I doubt it. There should be a way to set best answer AND rate the person for their knowledge and assistance.

To see an example of a "Live Filtered List" using formulas, go to the Data Validation section of this website: http://www.contextures.com/excelfil...

and download this file:

DataValFlexList.zip

Perhaps you can adapt the method used in that spreadsheet.

BTW...it can also be done with VBA.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

I tried it but, couldn't get it to work. All I get is either a blank cell or #NAME? or N/A. Coderre's spreadsheet is a little different as it employs the drop-down List function which I am not doing. I do not have advanced enough knowledge of Excel to understand the mechanics of the formulas. However, what I did understand (input range definition and cell references) I replaced to suit my spreadsheet and unfortunately the result was not successful. Thanks again to both of you for your help with this. I think I'll settle for the Advanced Filter function and not live filtering. Take care.

I started with a sheet that looks exactly like your Screen Capture.

Sportsin A1, your list of Sports in A2:A10,Playersin B1, your number values in B2:B10Totalsin A12I then copied the formula used in the downloaded spreadsheet into A13 and modified it to match your ranges:

=IF(SUMPRODUCT((A2:A10<>"")*ISERROR(MATCH(A2:A10,$A$12:A12,0)))<>0,INDEX(A2:A20,MATCH(TRUE,ISERROR(IF(ISBLANK(A2:A10),FALSE,MATCH(A2:A10,$A$12:$A12,0))),0),1),"")

I entered it using Ctrl-Shift-Enter since it is an array formula. This added the brackets { } around it.

I dragged it down to A18 to allow room for 2 new sports and got this:

A 12 Totals 13 Hockey 14 Baseball 15 Football 16 Soccer 17 18I modified Mike's SUMIF formula to account for blank cells in Column A and entered this in B13, then dragged it down to B18.

=IF(A13="","",SUMIF($A$2:$A$10,A13,$B$2:$B$10))

I now have this:

A B 12 Totals 13 Hockey 575 14 Baseball 48 15 Football 225 16 Soccer 290 17 18I then replaced Hockey in A9 with Ultimate and ended up with this:

A B 12 Totals 13 Hockey 395 14 Baseball 48 15 Football 225 16 Soccer 290 17 Ultimate 180 18There you go...A live list that updates with changes to your main list for both the Sport names and the Player values.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Here are some instruction on converting the DataValFlexList.xls

to somthing you could use.It's mostly just Cut & Paste with a bit of editing formulas.

Open a new workbook

Open the DataValFlexList.xls

In the DataValFlexList.xls, select columns A, B, C, & D

Select CopyGo to your new work book and Paste to column A

You should now have new columns A, B, C, & D from

the DataValFlexList.xls.Next, in your new workbook, select Column D,

which is theUser Inputcolumn and Cut

next, select Column A and Paste.Next, select Column B,

which is theList Itemscolumn and Cut

next, select Column D and Paste.Select cell A1 and change the heading to: Sport

Select cell B1 and input the heading: PlayersSelect cell D1 and change the heading to: Sport List

Select cell E2 and input TotalsIn cell E3 enter the formula:

=IF(D3="","",SUMIF($A$2:$A$100,D3,$B$2:$B$100))

NOTE: you will need to modify this if you go beyond 100 rows

in column ANow we need to change at least one of the Named Ranges:

In 2003, on the tool bar select Insert, Select Name, Define

There should be two Named Ranges:

First look at the named range:

rngUserInput

in the Refers To box at the bottom, it should show: =Sheet1!$A$2:$A$20

if it does not show that, change it.Next look at the named range:

LU_FlexibleList

in the Refers To box at the bottom, were going to delete the current formula

and insert this formula:=Sheet1!$D$2:INDEX(Sheet1!$D:$D,COUNTIF(Sheet1!$D:$D,"?*")+COUNT(Sheet1!$D:$D),1)

Click Close and were done.

Now, hopefully, when you enter a Sport in Column A

and the number of players in Column B

Column D will show the sport, and column E the total number

of players.MIKE

THAT'S IT! DerbyDad that worked like a charm! Thank you so much for the assistance. Mike, you deserve honourable mention for going above and beyond too. I tried to find a way to give you kudos on the site but, it doesn't have any such feature. I do appreciate the help though.

You both have gotten me to where I need to get to.

Thanks!

Ask Your Question

Weekly Poll

Do you think ride and car sharing are the future of transportation?

Discuss in The Lounge

Poll History