i think it might help to post the sample data in your post rather than a comment, it's a bit tricky to delimit from there. but i think this might be what you're asking.
if 1) you want a count of each column name as a row level pivot, you'll get only one valid cell for each row/column in the resulting pivot. the row context for the column value and the count ( distinct ) of the row will always match 1:1: example below:
Data (col2 is the formula =COUNTIFS($A$2:$A$7,A2)
and shows the number of times a
, b
, or c
appears in column A) :
+------+------+--------+------+
| col1 | col2 | col3 | col4 |
+------+------+--------+------+
| a | 3 | data | 1 |
| b | 2 | text | 1 |
| c | 1 | number | 1 |
| a | 3 | text | 1 |
| a | 3 | text | 1 |
| b | 2 | number | 1 |
+------+------+--------+------+
Pivot (notice how a
only shows data for 3, b
for 2, and c
for 1) :
+-------------+---------------+---+---+-------------+
| Sum of col4 | Column Labels | | | |
+-------------+---------------+---+---+-------------+
| Row Labels | a | b | c | Grand Total |
| 1 | | | 1 | 1 |
| 2 | | 2 | | 2 |
| 3 | 3 | | | 3 |
| Grand Total | 3 | 2 | 1 | 6 |
+-------------+---------------+---+---+-------------+
if however, 2) you are asking for each column with a corresponding "count of" label appended to it's name for the pivot table, Try adding a helper column in the source table =a2&" - "&countifs(A:A,A2)
(for our example in column E / col5
AND assuming the column A is desired pivot header). The new col5
will show values of a - 3
, b - 2
, and c - 3
(for our example). Make col5
the pivoted column or row and do the rest of your pivoting with the added label of how many rows you are using.
result of revision below:
Data :
+------+------+--------+------+-------+
| col1 | col2 | col3 | col4 | col5 |
+------+------+--------+------+-------+
| a | 3 | data | 1 | a - 3 |
| b | 2 | text | 1 | b - 2 |
| c | 1 | number | 1 | c - 1 |
| a | 3 | text | 1 | a - 3 |
| a | 3 | text | 1 | a - 3 |
| b | 2 | number | 1 | b - 2 |
+------+------+--------+------+-------+
Pivot :
+-------------+---------------+--------+------+-------------+
| Sum of col4 | Column Labels | | | |
+-------------+---------------+--------+------+-------------+
| Row Labels | data | number | text | Grand Total |
| a - 3 | 1 | | 2 | 3 |
| b - 2 | | 1 | 1 | 2 |
| c - 1 | | 1 | | 1 |
| Grand Total | 1 | 2 | 3 | 6 |
+-------------+---------------+--------+------+-------------+
hope it helps.
2Please clarify by providing a little sample data. – mtone – 2015-07-28T00:25:04.740
Table of Numbers
Count of X 1 2 4 6
Which is what I currently have, what I want, when I add another value is:
Count of X 1 2 4 6 Count of Y 2 4 1 7 – Dave – 2015-07-28T00:28:42.853
Would be clearer if you edited the original post with this info. – suspectus – 2015-07-28T06:48:10.500