How do I use a Pivot Table to count across multiple rows?

March 26, 2019 8:47 AM

Hi, Pivot tables tend to scramble my brain. How do I get it to count unique instances across multiple rows? Example and google sheet below the fold

The sample table looks like this:
vacation 1 vacation 2 vacation 3
Marc 1 jack jack fred
April 2 bob bob zack
April 5 bob fred bob

Editable google sheet here

And I'd like to know the total vacations for each person, stratified by month.

posted by cacofonie to Computers & Internet (7 answers total)

Use =COUNTIF
I stuck an example in there - fred's vacations, etc.
then you can pivot to get your desired result.
posted by entropone at 8:51 AM on March 26, 2019 [1 favorite]

oh, by month when the entry is just by date. missed that. i'll have to take a look after lunch.
posted by entropone at 9:04 AM on March 26, 2019

Best answer: Unfortunately your data is set up poorly for this - it's already pivoted in the wrong way, which makes things much harder. It should look like
column 1: date
column 2: vacation #
column 3: vacation person

So it should be
march 1 - vacation 1 - jack
march 1 - vacation 2 - jack
march 1 - vacation 3 - fred
april 2 - vacation 1 - bob
april 2 - vacation 2 - bob
april 2 - vacation 3 - zack
april 5 - vacation 1 - bob
april 5 - vacation 2 - fred
april 5 - vacation 3 - bob

And from there it's very simple to set up the pivot (date grouped by month in the rows; person in the columns, count of person in the values), or any other pivot you want (e.g. re-create your original data layout)

The good news is, if you are using a recent version of Excel it's relatively straightforward to unpivot your original data so that you can run a pivot table off of it how you want to - are you? If so I can explain how (it involves using the "Transform Data" from table function, and selecting "unpivot columns")
posted by brainmouse at 9:40 AM on March 26, 2019 [1 favorite]

The key definition here for pivot tables is:

All values of the same type are in the same column.
posted by ptm at 12:39 PM on March 26, 2019 [1 favorite]

As others have mentioned -- pivot tables summarize data -- and the way your data inputs are structured is already summarized (by date and "vacation number"). It's a bit confusing -- for instance, I'm not clear what it would mean for Jack to take two vacation days on March 1.

That said - if you have some weird reason why you need to input data in the format on the Example sheet...there is a weird way to calculate this using array formulas. Basically you COUNTIFS the employee name and month matches for EACH COLUMN with data, and add them all together. You need the array formula because normal execution of the MONTH() function (to check the month) only accepts one cell as an input, but you need to check a range of cells.*

It would be better to restructure your data more simply as: DATE | EMPLOYEE. Then you can create a pivot table very easily that summarizes # of Vacay days by employee and month. I added an example of how to structure that table and create a Pivot. When you make the pivot table, you need to use the design options to group the dates by the month (in Gsheets, you right click on the table and select Create Pivot Date Group)
posted by voiceofreason at 3:32 PM on March 26, 2019

Everyone else has provided excellent solutions, so I would instead like to provide you with a useful to arrange your data better in the future. The tidy data approach is the way to go: arrange your data where every column is a variable and every row is an observation. Makes operations like pivots easy. See the full paper here: Tidy Data.
posted by skye.dancer at 7:44 PM on March 26, 2019

Response by poster: Thanks Brainmouse, that did it!

Yes, the data was provided to me this way, unfortunately.

I used Powerquery to unpivot it and then repivot as needed.

Thanks for introducing me to a powerful new tool!
posted by cacofonie at 1:35 PM on March 28, 2019