# Excel Question

**URL:** <https://boards.straightdope.com/t/excel-question/554915>\
**Category:** Factual Questions\
**Created:** [September 24, 2010, 6:39pm UTC](https://boards.straightdope.com/t/excel-question/554915 "2010-09-24T18:39:16Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![cmosdes](https://avatars.discourse-cdn.com/v4/letter/c/a587f6/32.png) [@cmosdes](https://boards.straightdope.com/u/cmosdes)\
**Post date:** [September 24, 2010, 6:39pm UTC](https://boards.straightdope.com/t/excel-question/554915/1 "2010-09-24T18:39:16Z")

</div>

I have a table I use to track player positions for my soccer team.

The table is formatted like:

```auto

Player Q1 Q2 Q3 Q4
P1 K K SW SW
P2 RF RF RF OUT

etc.

```

What I want is a report, by quarter, of the number of players assigned to each position:

```auto

     K SW ST RD LD LM RM RF LF OUT
Q1 1 1 1 1 1 2 2 1 1 4
Q2 1 1 1 1 1 2 2 1 1 4

```

Doe anyone know how to use pivot tables to do this or some other slick way to generate such a report? It will help me figure out if I’ve assigned too many players to a given position for a given quarter.

---

<div class="post-metadata">

**Author:** ![Darth\_Panda](https://avatars.discourse-cdn.com/v4/letter/d/ee7513/32.png) [@Darth\_Panda](https://boards.straightdope.com/u/Darth_Panda)\
**Post date:** [September 24, 2010, 6:54pm UTC](https://boards.straightdope.com/t/excel-question/554915/2 "2010-09-24T18:54:52Z")

</div>

You can use the countif function

=countif(x,y)

You’ll need to enter 4 formulas, 1 for each quarter

For x, enter the array corresponding to your first table for the quarter in question, making sure to use F4 to add dollar signs -\> $B$2:$B$3

For y, enter the cell with the position K in it -\> C8 for example (no $ here, or just on row, but not on column)

So, you’ll enter these four formulas under the K in your second chart shown above, and then drag them out to the right.

Then you’re done.

eta: you could get around having to enter 4 different formulas by building offsets into the countif function or by reversing the layout of the original chart, but it’s only 4 things, so whatever

---

<div class="post-metadata">

**Author:** ![Duke](https://avatars.discourse-cdn.com/v4/letter/d/e8c25b/32.png) [@Duke](https://boards.straightdope.com/u/Duke)\
**Post date:** [September 24, 2010, 6:56pm UTC](https://boards.straightdope.com/t/excel-question/554915/3 "2010-09-24T18:56:35Z")

</div>

It can be done fairly easily in a pivot table, but I’d suggest changing the position names to numbers–if for no other reason than it would make it easier to read. When you build the pivot table, you’ll put the positions into the “column labels” section, and the quarters into the “row labels” section of the pivot table field list. Changing “K” for 'keeper to “1”, “SW” for sweeper (?) to “2”, and so on stops you from having to specially sort them…otherwise you’d have to have a custom alphabetical sort.

Format the column values to “sum of items”, and you should be good to go.

---

<div class="post-metadata">

**Author:** ![Munch](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/munch/32/5281_2.png) [@Munch](https://boards.straightdope.com/u/Munch)\
**Post date:** [September 24, 2010, 6:59pm UTC](https://boards.straightdope.com/t/excel-question/554915/4 "2010-09-24T18:59:29Z")

</div>

Why would a numerical sort be any easier than alphabetical sort?

cmosdes: Which version of Excel are you running? MS decided to change how to set up a pivot chart when they went to 2007.

---

<div class="post-metadata">

**Author:** ![cmosdes](https://avatars.discourse-cdn.com/v4/letter/c/a587f6/32.png) [@cmosdes](https://boards.straightdope.com/u/cmosdes)\
**Post date:** [September 24, 2010, 7:26pm UTC](https://boards.straightdope.com/t/excel-question/554915/5 "2010-09-24T19:26:30Z")

</div>

> [@Darth\_Panda](#):
>
> You can use the countif function
> 
> =countif(x,y)
> 
> You’ll need to enter 4 formulas, 1 for each quarter
> 
> For x, enter the array corresponding to your first table for the quarter in question, making sure to use F4 to add dollar signs -\> $B$2:$B$3
> 
> For y, enter the cell with the position K in it -\> C8 for example (no $ here, or just on row, but not on column)
> 
> So, you’ll enter these four formulas under the K in your second chart shown above, and then drag them out to the right.
> 
> Then you’re done.
> 
> eta: you could get around having to enter 4 different formulas by building offsets into the countif function or by reversing the layout of the original chart, but it’s only 4 things, so whatever

I’m hoping to use the data in the table to build the column headings, though. I could build a second table with the headings “K”, “SW”, etc., but since that data is already in original table, I want my pivot table to automagically build that. I can do this with a different stat program I have, but I want to be able to share this table with others who won’t have the stat program.

**Duke** How do I put “K”, “SW”, etc. as the column labels? When I select the data area, I don’t see an option to say “these values are the labels”.

**Munch** 2003 version.

---

<div class="post-metadata">

**Author:** ![Darth\_Panda](https://avatars.discourse-cdn.com/v4/letter/d/ee7513/32.png) [@Darth\_Panda](https://boards.straightdope.com/u/Darth_Panda)\
**Post date:** [September 24, 2010, 7:30pm UTC](https://boards.straightdope.com/t/excel-question/554915/6 "2010-09-24T19:30:34Z")

</div>

> [@cmosdes](#):
>
> > [@Darth\_Panda](#):
> >
> > You can use the countif function
> > 
> > =countif(x,y)
> > 
> > You’ll need to enter 4 formulas, 1 for each quarter
> > 
> > For x, enter the array corresponding to your first table for the quarter in question, making sure to use F4 to add dollar signs -\> $B$2:$B$3
> > 
> > For y, enter the cell with the position K in it -\> C8 for example (no $ here, or just on row, but not on column)
> > 
> > So, you’ll enter these four formulas under the K in your second chart shown above, and then drag them out to the right.
> > 
> > Then you’re done.
> > 
> > eta: you could get around having to enter 4 different formulas by building offsets into the countif function or by reversing the layout of the original chart, but it’s only 4 things, so whatever
> 
> I’m hoping to use the data in the table to build the column headings, though. I could build a second table with the headings “K”, “SW”, etc., but since that data is already in original table, I want my pivot table to automagically build that. I can do this with a different stat program I have, but I want to be able to share this table with others who won’t have the stat program.
> 
> **Duke** How do I put “K”, “SW”, etc. as the column labels? When I select the data area, I don’t see an option to say “these values are the labels”.
> 
> **Munch** 2003 version.

It seems like typing in the headings would have taken less time than your post…

---

<div class="post-metadata">

**Author:** ![cmosdes](https://avatars.discourse-cdn.com/v4/letter/c/a587f6/32.png) [@cmosdes](https://boards.straightdope.com/u/cmosdes)\
**Post date:** [September 24, 2010, 10:44pm UTC](https://boards.straightdope.com/t/excel-question/554915/7 "2010-09-24T22:44:25Z")

</div>

> [@Darth\_Panda](#):
>
> > [@cmosdes](#):
> >
> > I’m hoping to use the data in the table to build the column headings, though. I could build a second table with the headings “K”, “SW”, etc., but since that data is already in original table, I want my pivot table to automagically build that. I can do this with a different stat program I have, but I want to be able to share this table with others who won’t have the stat program.
> > 
> > **Duke** How do I put “K”, “SW”, etc. as the column labels? When I select the data area, I don’t see an option to say “these values are the labels”.
> > 
> > **Munch** 2003 version.
> 
> It seems like typing in the headings would have taken less time than your post…

Absolutely. However, this really isn’t a question about how to this for this particular instance, as I had already found a way to do it by using 4 pivot tables. I was hoping to find a way to do it that will save me time the next time I need to do something similar but maybe that has 30 or 40 different categories instead of 9. I do appreciate the response, though. Your countif() option was not one I had previously known and it better than the solution I came up with.

---

<div class="post-metadata">

**Author:** ![Darth\_Panda](https://avatars.discourse-cdn.com/v4/letter/d/ee7513/32.png) [@Darth\_Panda](https://boards.straightdope.com/u/Darth_Panda)\
**Post date:** [September 25, 2010, 1:24am UTC](https://boards.straightdope.com/t/excel-question/554915/8 "2010-09-25T01:24:33Z")

</div>

> [@cmosdes](#):
>
> > [@Darth\_Panda](#):
> >
> > It seems like typing in the headings would have taken less time than your post…
> 
> Absolutely. However, this really isn’t a question about how to this for this particular instance, as I had already found a way to do it by using 4 pivot tables. I was hoping to find a way to do it that will save me time the next time I need to do something similar but maybe that has 30 or 40 different categories instead of 9. I do appreciate the response, though. Your countif() option was not one I had previously known and it better than the solution I came up with.

Fair enough - I tend to prefer formulas over pivot tables for a bunch of reasons.

But really, if you want to deal with large amounts of data and outputting it in different forms (sorting by different cateories like you’re talking about), Access is a much more powerful tool.

---

<div class="post-metadata">

**Author:** ![Hal\_Briston](https://avatars.discourse-cdn.com/v4/letter/h/a9a28c/32.png) [@Hal\_Briston](https://boards.straightdope.com/u/Hal_Briston)\
**Post date:** [September 25, 2010, 1:44am UTC](https://boards.straightdope.com/t/excel-question/554915/9 "2010-09-25T01:44:46Z")

</div>

Rather than start a second thread with the same title, I’ll just put this in here.

Take a look at [this screenshot](http://www.sirblah.com/misc/excel.jpg). Is there an easy way to remove blank cells, shifting all the "X"s to the left side of each row?

---

<div class="post-metadata">

**Author:** ![Darth\_Panda](https://avatars.discourse-cdn.com/v4/letter/d/ee7513/32.png) [@Darth\_Panda](https://boards.straightdope.com/u/Darth_Panda)\
**Post date:** [September 25, 2010, 1:56am UTC](https://boards.straightdope.com/t/excel-question/554915/10 "2010-09-25T01:56:45Z")

</div>

You would create a second chart on another sheet, and in cell A1 you would put this formula

=IF(COUNTIF(Sheet1!A$1:P$1,“X”)\>COLUMN(A1)-1,“X”,"")

Then drag the forumla to cover the same cells as where the data is entered on the other sheet. Note the use of the $ signs. If your original sheet isn’t Sheet1, you’ll need to adjust as necessary.

This does require the use of a duplicated chart. If you want to actually move the Xs within the same chart, you’ll need to use a macro.

eta: the macro would just create a sheet set up like this, then copy as values over the original sheet, and then delete the sheet with the formulas