Pivot Table: count multiple columns

R

rmb4466

New Member
Joined
Feb 1, 2010
Messages
11
I've got a worksheet in which each row represents a specific task. For each task, there will be at least one assignee and perhaps up to four assignees. I'd like to make a pivot table that will count the number of times each assignee has been assigned to a task. Currently, when I add each assignee column to the pivot table, they nest under each other. Is there a way to have them count all together instead of nested?

Current worksheet looks like this:
[TABLE="width: 700"]
<tbody>[TR]
[TD]Task ID
[/TD]
[TD]Date
[/TD]
[TD]Location
[/TD]
[TD]Task
[/TD]
[TD]Assignee1
[/TD]
[TD]Assignee2
[/TD]
[TD]
Assignee​
<strike></strike>3
[/TD]
[TD]
Assignee​
​4<strike></strike>
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]1 Oct 2018
[/TD]
[TD]Kansas City, MO
[/TD]
[TD]On-site visit
[/TD]
[TD]Jon Dough
[/TD]
[TD]Jane Doh
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]13 Oct 2018
[/TD]
[TD]Arkansas City, KS[/TD]
[TD]Telephone consult
[/TD]
[TD]Rick Row
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]20 Oct 2018
[/TD]
[TD]Noel, MO
[/TD]
[TD]
On-site visit​
<strike></strike>
[/TD]
[TD]
Jon Dough​
<strike></strike>
[/TD]
[TD]<strike></strike>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]3 Nov 2018
[/TD]
[TD]Oark, AR
[/TD]
[TD]Research
[/TD]
[TD]
Jane Doh​
<strike></strike>
[/TD]
[TD]<strike></strike>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]16 Nov 2018
[/TD]
[TD]Springfield, MO
[/TD]
[TD]
On-site visit​
<strike></strike>
[/TD]
[TD]
Rick Row​
​
<strike></strike>
[/TD]
[TD]
Jon Dough​
<strike></strike>
[/TD]
[TD]<strike></strike>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]4 Dec 2018
[/TD]
[TD]Bogue, KS
[/TD]
[TD]
Telephone consult​
[/TD]
[TD]Jo Schmoe
[/TD]
[TD]
Jane Doh​
<strike></strike>
[/TD]
[TD]
Jon Dough​
[/TD]
[TD]
Rick Row​
<strike></strike>
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]7 Dec 2018
[/TD]
[TD]Miami, OK
[/TD]
[TD]
Research​
<strike></strike>
[/TD]
[TD]
Jo Schmoe​
<strike></strike>
[/TD]
[TD]<strike></strike>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]19 Dec 2018
[/TD]
[TD]Warsaw, MO
[/TD]
[TD]
On-site visit​
<strike></strike>
[/TD]
[TD]
Jane Doh​
<strike></strike>
[/TD]
[TD]<strike></strike>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]28 Dec 2018
[/TD]
[TD]Kirksville, MO
[/TD]
[TD]
On-site visit​
<strike></strike>
[/TD]
[TD]
Jo Schmoe​
<strike></strike>
[/TD]
[TD]
Jon Dough​
<strike></strike>
[/TD]
[TD]<strike></strike>
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I'm trying to make a pivot table that looks like this:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Count of Task
[/TD]
[TD]Column Labels
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row Labels
[/TD]
[TD](blank)
[/TD]
[TD]Oct
[/TD]
[TD]Nov
[/TD]
[TD]Dec
[/TD]
[TD]Grand Total
[/TD]
[/TR]
[TR]
[TD].On-site visit
[/TD]
[TD][/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[TD]8
[/TD]
[/TR]
[TR]
[TD]
..Jane Doh​
[/TD]
[TD]<strike></strike>
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]
..Jo Schmoe​
<strike style="background-color: transparent; border-collapse: collapse; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; width: auto; word-spacing: 0px;"></strike>
[/TD]
[TD]<strike></strike>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]
..Jon Dough​
[/TD]
[TD]<strike></strike>
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]
..Rick Row​
<strike style="background-color: transparent; border-collapse: collapse; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; width: auto; word-spacing: 0px;"></strike>
[/TD]
[TD]<strike></strike><strike></strike>
[/TD]
[TD][/TD]
[TD]1
[/TD]
[TD][/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD].Research
[/TD]
[TD][/TD]
[TD][/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]
..Jane Doh​
[/TD]
[TD]<strike></strike>
[/TD]
[TD][/TD]
[TD]1
[/TD]
[TD][/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]
..Jo Schmoe​
[/TD]
[TD]<strike></strike>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]
.Telephone consult​
[/TD]
[TD]<strike></strike><strike></strike>
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD]4
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]
..Jane Doh​
[/TD]
[TD]<strike></strike><strike></strike>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]
..Jo Schmoe​
<strike style="background-color: transparent; border-collapse: collapse; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: line-through; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; width: auto; word-spacing: 0px;"></strike>
[/TD]
[TD]<strike></strike><strike></strike>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]
..Jon Dough​
[/TD]
[TD]<strike></strike>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]
..Rick Row​
[/TD]
[TD]<strike></strike>
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]Grand Total
[/TD]
[TD][/TD]
[TD]4
[/TD]
[TD]3
[/TD]
[TD]8
[/TD]
[TD]15
[/TD]
[/TR]
</tbody>[/TABLE]


Thanks,
--Ryan
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sort by date Sort by votes
is that what you want?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Task ID[/td][td=bgcolor:#5B9BD5]Date[/td][td=bgcolor:#5B9BD5]Location[/td][td=bgcolor:#5B9BD5]Task[/td][td=bgcolor:#5B9BD5]Assignee1[/td][td=bgcolor:#5B9BD5]Assignee2[/td][td=bgcolor:#5B9BD5]Assignee 3[/td][td=bgcolor:#5B9BD5]Assignee 4[/td][td][/td][td=bgcolor:#DDEBF7]Count of Value[/td][td=bgcolor:#DDEBF7]Column Labels[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
1​
[/td][td=bgcolor:#DDEBF7]
01-Oct-18​
[/td][td=bgcolor:#DDEBF7]Kansas City, MO[/td][td=bgcolor:#DDEBF7]On-site visit[/td][td=bgcolor:#DDEBF7]Jon Dough[/td][td=bgcolor:#DDEBF7]Jane Doh[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td][/td][td=bgcolor:#DDEBF7]Row Labels[/td][td=bgcolor:#DDEBF7]October[/td][td=bgcolor:#DDEBF7]November[/td][td=bgcolor:#DDEBF7]December[/td][td=bgcolor:#DDEBF7]Grand Total[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
2​
[/td][td]
13-Oct-18​
[/td][td]Arkansas City, KS[/td][td]Telephone consult[/td][td]Rick Row[/td][td][/td][td][/td][td][/td][td][/td][td]On-site visit[/td][td]
3​
[/td][td]
2​
[/td][td]
3​
[/td][td]
8​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
3​
[/td][td=bgcolor:#DDEBF7]
20-Oct-18​
[/td][td=bgcolor:#DDEBF7]Noel, MO[/td][td=bgcolor:#DDEBF7]On-site visit[/td][td=bgcolor:#DDEBF7]Jon Dough[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td][/td][td] Jane Doh[/td][td]
1​
[/td][td][/td][td]
1​
[/td][td]
2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
4​
[/td][td]
03-Nov-18​
[/td][td]Oark, AR[/td][td]Research[/td][td]Jane Doh[/td][td][/td][td][/td][td][/td][td][/td][td] Jo Schmoe[/td][td][/td][td][/td][td]
1​
[/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
5​
[/td][td=bgcolor:#DDEBF7]
16-Nov-18​
[/td][td=bgcolor:#DDEBF7]Springfield, MO[/td][td=bgcolor:#DDEBF7]On-site visit[/td][td=bgcolor:#DDEBF7]Rick Row[/td][td=bgcolor:#DDEBF7]Jon Dough[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td][/td][td] Jon Dough[/td][td]
2​
[/td][td]
1​
[/td][td]
1​
[/td][td]
4​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
6​
[/td][td]
04-Dec-18​
[/td][td]Bogue, KS[/td][td]Telephone consult[/td][td]Jo Schmoe[/td][td]Jane Doh[/td][td]Jon Dough[/td][td]Rick Row[/td][td][/td][td] Rick Row[/td][td][/td][td]
1​
[/td][td][/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
7​
[/td][td=bgcolor:#DDEBF7]
07-Dec-18​
[/td][td=bgcolor:#DDEBF7]Miami, OK[/td][td=bgcolor:#DDEBF7]Research[/td][td=bgcolor:#DDEBF7]Jo Schmoe[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td][/td][td]Research[/td][td][/td][td]
1​
[/td][td]
1​
[/td][td]
2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
8​
[/td][td]
19-Dec-18​
[/td][td]Warsaw, MO[/td][td]On-site visit[/td][td]Jane Doh[/td][td][/td][td][/td][td][/td][td][/td][td] Jane Doh[/td][td][/td][td]
1​
[/td][td][/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
9​
[/td][td=bgcolor:#DDEBF7]
28-Dec-18​
[/td][td=bgcolor:#DDEBF7]Kirksville, MO[/td][td=bgcolor:#DDEBF7]On-site visit[/td][td=bgcolor:#DDEBF7]Jo Schmoe[/td][td=bgcolor:#DDEBF7]Jon Dough[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td][/td][td] Jo Schmoe[/td][td][/td][td][/td][td]
1​
[/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]Telephone consult[/td][td]
1​
[/td][td][/td][td]
4​
[/td][td]
5​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td] Jane Doh[/td][td][/td][td][/td][td]
1​
[/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td] Jo Schmoe[/td][td][/td][td][/td][td]
1​
[/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td] Jon Dough[/td][td][/td][td][/td][td]
1​
[/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td] Rick Row[/td][td]
1​
[/td][td][/td][td]
1​
[/td][td]
2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#DDEBF7]Grand Total[/td][td=bgcolor:#DDEBF7]
4​
[/td][td=bgcolor:#DDEBF7]
3​
[/td][td=bgcolor:#DDEBF7]
8​
[/td][td=bgcolor:#DDEBF7]
15​
[/td][/tr]
[/table]

Done with PowerQuery

M-code
Code: 
[SIZE=1]
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Extracted Month Name" = Table.TransformColumns(Source, {{"Date", each Date.MonthName(_), type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Month Name",{"Task ID", "Location"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Date", "Task"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"[/SIZE]

then PivotTable from external data source Query - Table1
 
Upvote 0
It appears so, though it doesn't look like a pivot table as I'm familiar with them (to the limited degree that I am familiar with them). Using your method, does the table incorporate new data automatically? (i.e., if we add a new entry for Rick Row doing a Research visit in January, would it add a new row for Rick Row under Research and a new column for January?)

I'm unfamiliar with PowerQuery...going to have to do some poking around.

Thanks for your response!
 
Upvote 0
You mean like this?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Task ID[/td][td=bgcolor:#5B9BD5]Date[/td][td=bgcolor:#5B9BD5]Location[/td][td=bgcolor:#5B9BD5]Task[/td][td=bgcolor:#5B9BD5]Assignee1[/td][td=bgcolor:#5B9BD5]Assignee2[/td][td=bgcolor:#5B9BD5]Assignee 3[/td][td=bgcolor:#5B9BD5]Assignee 4[/td][td][/td][td=bgcolor:#DDEBF7]Count of Value[/td][td=bgcolor:#DDEBF7]Column Labels[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
1​
[/td][td=bgcolor:#DDEBF7]
01-Oct-18​
[/td][td=bgcolor:#DDEBF7]Kansas City, MO[/td][td=bgcolor:#DDEBF7]On-site visit[/td][td=bgcolor:#DDEBF7]Jon Dough[/td][td=bgcolor:#DDEBF7]Jane Doh[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td][/td][td=bgcolor:#DDEBF7]Row Labels[/td][td=bgcolor:#DDEBF7]October[/td][td=bgcolor:#DDEBF7]November[/td][td=bgcolor:#DDEBF7]December[/td][td=bgcolor:#DDEBF7]January[/td][td=bgcolor:#DDEBF7]Grand Total[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
2​
[/td][td]
13-Oct-18​
[/td][td]Arkansas City, KS[/td][td]Telephone consult[/td][td]Rick Row[/td][td][/td][td][/td][td][/td][td][/td][td]On-site visit[/td][td]
3​
[/td][td]
2​
[/td][td]
3​
[/td][td][/td][td]
8​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
3​
[/td][td=bgcolor:#DDEBF7]
20-Oct-18​
[/td][td=bgcolor:#DDEBF7]Noel, MO[/td][td=bgcolor:#DDEBF7]On-site visit[/td][td=bgcolor:#DDEBF7]Jon Dough[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td][/td][td] Jane Doh[/td][td]
1​
[/td][td][/td][td]
1​
[/td][td][/td][td]
2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
4​
[/td][td]
03-Nov-18​
[/td][td]Oark, AR[/td][td]Research[/td][td]Jane Doh[/td][td][/td][td][/td][td][/td][td][/td][td] Jo Schmoe[/td][td][/td][td][/td][td]
1​
[/td][td][/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
5​
[/td][td=bgcolor:#DDEBF7]
16-Nov-18​
[/td][td=bgcolor:#DDEBF7]Springfield, MO[/td][td=bgcolor:#DDEBF7]On-site visit[/td][td=bgcolor:#DDEBF7]Rick Row[/td][td=bgcolor:#DDEBF7]Jon Dough[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td][/td][td] Jon Dough[/td][td]
2​
[/td][td]
1​
[/td][td]
1​
[/td][td][/td][td]
4​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
6​
[/td][td]
04-Dec-18​
[/td][td]Bogue, KS[/td][td]Telephone consult[/td][td]Jo Schmoe[/td][td]Jane Doh[/td][td]Jon Dough[/td][td]Rick Row[/td][td][/td][td] Rick Row[/td][td][/td][td]
1​
[/td][td][/td][td][/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
7​
[/td][td=bgcolor:#DDEBF7]
07-Dec-18​
[/td][td=bgcolor:#DDEBF7]Miami, OK[/td][td=bgcolor:#DDEBF7]Research[/td][td=bgcolor:#DDEBF7]Jo Schmoe[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td][/td][td]Research[/td][td][/td][td]
1​
[/td][td]
1​
[/td][td]
1​
[/td][td]
3​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
8​
[/td][td]
19-Dec-18​
[/td][td]Warsaw, MO[/td][td]On-site visit[/td][td]Jane Doh[/td][td][/td][td][/td][td][/td][td][/td][td] Jane Doh[/td][td][/td][td]
1​
[/td][td][/td][td][/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
9​
[/td][td=bgcolor:#DDEBF7]
28-Dec-18​
[/td][td=bgcolor:#DDEBF7]Kirksville, MO[/td][td=bgcolor:#DDEBF7]On-site visit[/td][td=bgcolor:#DDEBF7]Jo Schmoe[/td][td=bgcolor:#DDEBF7]Jon Dough[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td][/td][td] Jo Schmoe[/td][td][/td][td][/td][td]
1​
[/td][td][/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#FFFF00]
10​
[/td][td=bgcolor:#FFFF00]
10-Jan-18​
[/td][td=bgcolor:#FFFF00]Any[/td][td=bgcolor:#FFFF00]Research[/td][td=bgcolor:#FFFF00]Rick Row[/td][td=bgcolor:#FFFF00][/td][td=bgcolor:#FFFF00][/td][td=bgcolor:#FFFF00][/td][td][/td][td=bgcolor:#FFFF00] Rick Row[/td][td=bgcolor:#FFFF00][/td][td=bgcolor:#FFFF00][/td][td=bgcolor:#FFFF00][/td][td=bgcolor:#FFFF00]
1​
[/td][td=bgcolor:#FFFF00]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]Telephone consult[/td][td]
1​
[/td][td][/td][td]
4​
[/td][td][/td][td]
5​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td] Jane Doh[/td][td][/td][td][/td][td]
1​
[/td][td][/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td] Jo Schmoe[/td][td][/td][td][/td][td]
1​
[/td][td][/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td] Jon Dough[/td][td][/td][td][/td][td]
1​
[/td][td][/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td] Rick Row[/td][td]
1​
[/td][td][/td][td]
1​
[/td][td][/td][td]
2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#DDEBF7]Grand Total[/td][td=bgcolor:#DDEBF7]
4​
[/td][td=bgcolor:#DDEBF7]
3​
[/td][td=bgcolor:#DDEBF7]
8​
[/td][td=bgcolor:#DDEBF7]
1​
[/td][td=bgcolor:#DDEBF7]
16​
[/td][/tr]
[/table]

example excel file
 
Last edited:
Upvote 0
Great info, thanks!

I think I've pretty much got it set up and reporting properly. One quick question, though: when I make changes to the original dataset, do I always need to refresh the query table and then the pivot table or is there a way to have the automatically refresh?
 
Upvote 0
:) Both

Refresh PivotTable (you don't need refresh Query Table)
or
You can use any vba code to refresh PivotTable on Worksheet_Change but IMHO it doesn't make any sense with a big data
or
PivotTable option - Data - Refresh data when opening the file
 
Last edited:
Upvote 0
Got it. Going to have to read your links on Get & Transform and Power Query a bit more closely later.

Thanks for your help!
 
Upvote 0
You are welcome

Power Query aka Get&Transform - it's the same

Have a nice day
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Lookup If Blank Or only have certain value
2
Replies
14
Views
2K
sandy666
sandy666
R
  • Question Question
MAXIF and return another column
2 3
Replies
21
Views
3K
Aryatect
Aryatect
A
  • Question Question
Formula for conditional formating - NOT AS EASY AS IT SEEMS
Replies
3
Views
908
Eric W
Eric W
J
  • Question Question
COUNTIFS Hours and minutes in range
Replies
3
Views
901
CyrusTheVirus
CyrusTheVirus
E
  • Question Question
VBA autofilter 1004 No cells were found
Replies
5
Views
1K
Fluff
Fluff

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top