PIVOT Count Only " - " in table

surkdidat

Well-known Member
Joined
Oct 1, 2011
Messages
579
Office Version
  1. 2016
Is there anyway of creating a Pivot to count the number of occurrences of "-" when the columns and rows match?

I know I can do this by INDEX MATCH MATCH (or similar) but would prefer it in a pivot, that way I can get asy access on the data when clicking on the info?
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Wondeful example, really :) but where are the data?
 
Last edited:
Upvote 0
so I want to create a Pivot with Names in the rows, and the categories as columns

NameContactedAppointmentSale
Claire121
Dan1


IDNameDateContacted?AppointmentSale
111Adam02/06/2019YesYesYes
222Ben01/06/2019YesYesYes
333Claire02/06/2019Yes--
444Dan02/06/2019Yes-Yes
555Claire02/06/2019--Yes


In this Pivot I want it to say



any example of source for your "pivot" ?
 
Upvote 0
something like this?

IDNameDateContacted?AppointmentSaleNameValueAppointmentSaleContacted?
111​
Adam
02/06/2019​
YesYesYesClaire-
2​
1​
1​
222​
Ben
01/06/2019​
YesYesYesDan-
1​
333​
Claire
02/06/2019​
Yes--
444​
Dan
02/06/2019​
Yes-Yes
555​
Claire
02/06/2019​
--Yes
 
Upvote 0
Yes, please, thank you :)

something like this?

IDNameDateContacted?AppointmentSaleNameValueAppointmentSaleContacted?
111​
Adam
02/06/2019​
YesYesYesClaire-
2​
1​
1​
222​
Ben
01/06/2019​
YesYesYesDan-
1​
333​
Claire
02/06/2019​
Yes--
444​
Dan
02/06/2019​
Yes-Yes
555​
Claire
02/06/2019​
--Yes
 
Upvote 0
so use PowerQuery (Get&Transform)

Code:
[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Name", type text}, {"Date", type date}, {"Contacted?", type text}, {"Appointment", type text}, {"Sale", type text}}),
    Unpivot = Table.UnpivotOtherColumns(Type, {"ID", "Name", "Date"}, "Attribute", "Value"),
    Filter = Table.SelectRows(Unpivot, each ([Value] = "-")),
    Group = Table.Group(Filter, {"Name", "Attribute", "Value"}, {{"Count", each Table.RowCount(_), type number}}),
    Pivot = Table.Pivot(Group, List.Distinct(Group[Attribute]), "Attribute", "Count")
in
    Pivot[/SIZE]
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,099
Members
448,548
Latest member
harryls

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