Pivot Table

[alag76]

Hi everyone,

Can I use a Pivot Table to return the number of records meeting a specific criteria? In the screenshot example, I would like to have an additional pivot table column that shows the number of records where the days outstanding that are greater than 14 days for each ID number. For example, for ID number 2 it would be 3 since there are 3 records with 15. Thank you!!

 

[alansidman]

Cannot manipulate data in a picture. Please represent using XL2BB so that we don't have to recreate your file to figure out a solution for you.

 

[alag76]

Book1
	A	B	C	D	E	F	G	H
1	Site ID	Query ID	Days Outstanding			Row Labels	Count of Query ID	Days outstanding >14
2	2	1	2			2	5	2
3	2	2	2			4	8	5
4	2	3	2			5	8	1
5	2	4	15			6	9	3
6	2	5	15			Grand Total	30	11
7	4	6	10
8	4	7	10
9	4	8	10
10	4	9	100
11	4	10	100
12	4	11	100
13	4	12	100
14	4	13	100
15	5	14	6
16	5	15	6
17	5	16	6
18	5	17	6
19	5	18	6
20	5	19	6
21	5	20	6
22	5	21	99
23	6	22	7
24	6	23	7
25	6	24	7
26	6	25	7
27	6	26	7
28	6	27	7
29	6	28	20
30	6	29	20
31	6	30	20
Sheet1

Cell Formulas
Range	Formula
H6	H6	=SUM(H2:H5)

 

[alansidman]

Here is a Power Query Solution

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Site ID", Int64.Type}, {"Query ID", Int64.Type}, {"Days Outstanding", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if[Days Outstanding]>14 then 1 else null),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Site ID"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"Days > 14", each List.Sum([Custom]), type nullable number}})
in
    #"Grouped Rows"

Book6
	F	G	H
1	Row Labels	Count of Query ID	Days outstanding >14
2	2	5	2
3	4	8	5
4	5	8	1
5	6	9	3
6	Grand Total	30	11
7
8	Site ID	Count	Days > 14
9	2	5	2
10	4	8	5
11	5	8	1
12	6	9	3
Sheet1

Cell Formulas
Range	Formula
H6	H6	=SUM(H2:H5)

 

[alag76]

Here is a Power Query Solution

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Site ID", Int64.Type}, {"Query ID", Int64.Type}, {"Days Outstanding", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if[Days Outstanding]>14 then 1 else null),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Site ID"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"Days > 14", each List.Sum([Custom]), type nullable number}})
in
    #"Grouped Rows"

Book6
	F	G	H
1	Row Labels	Count of Query ID	Days outstanding >14
2	2	5	2
3	4	8	5
4	5	8	1
5	6	9	3
6	Grand Total	30	11
7
8	Site ID	Count	Days > 14
9	2	5	2
10	4	8	5
11	5	8	1
12	6	9	3
Sheet1

Cell Formulas
Range	Formula
H6	H6	=SUM(H2:H5)

Thank you! I'm not familiar with PowerQuery but I will research on how to add.

 

[alansidman]

FYI

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.