Add cumulative counts to a pivot table in excel

Question

I have data in excel that looks like this:

Shop   | Division    | Launch Year
-------+-------------+-------------
Shop 1 | Division 1  | 2005
Shop 2 | Division 1  | 2006
Shop A | Division 2  | 2005
Shop B | Division 2  | 2007
...

And so on. There are about 100 shops total across 4 divisions. I need to get a table showing how many stores are open at the end of each year, per division. With a pivot table, I can easily show how many new stores were opened during that year, but not how many total.

I know how to add running totals to a pivot table, but in my case that doesn't work, as I don't have numbers to add - I need to add running counts instead. How can get that done?

I'm looking for this end result:

Year | Division 1 | Division 2 | ...
-----+------------+------------+----
2005 | 1          | 1          |
2006 | 2          | 1          |
2007 | 2          | 2          |
...

Answer 1

A running count is a running total of 1s.
If the data allows for it, just throw down an additional column full of 1s and use those in your pivot for a running total.

Answer 2

If your table is relatively static (columns and rows don't really change much) then use those those column headers and row headers. In the body, use formulas like (in pseudo code):

=countifs('Launch Year',"<="&'row header', 'Division', 'column header')

If you use the proper anchoring, you can build the formula once, and copy and paste it through out the entire body.

Answer 3

You don't need a pivot table for this.

Create your table as shown in your desired output. Populate the data area with the following array formula, entered with Ctrl Shift Enter and then copied across and down:

=SUMPRODUCT(--IF($C$2:$C$5<=$E2,1,0),IF($B$2:$B$5=F$1,1,0))

Update cell references as needed, paying mind to anchoring.

Sumproduct function documentation from MS.

You're using IF statements two generate two identically-sized arrays of matching values (the -- turns TRUE to 1 and FALSE to 0), multiplying them against each other to find records that match both criteria, then summing the resultant list of 1's and 0's to get your total.

EDIT: To respond to your comment/question

The setup shown here so far is a small data and results table for proof of concept. To make this more fully extensible, you'll probably turn the input table into a named table. To do this, select any cell in the relevant range (example: B4 in the setup as shown) and press Ctrl + T, then confirm in the resultant modal window. That will both make it easier to write cell references and automatically update any cell references to those data ranges as data gets added.

You can also make the results table a Table, too, using the same method. This way, when you add a new year, all you have to do is enter the year in the next row underneath that table and all the formulas will automatically paste down:

Note that this automatic updating of the output table will not work for adding a new Division. The table will expand to accommodate it, but you will have to manually copy the SUMPRODUCT formula into the new field.

Good luck and enjoy!