PIVOT TABLES-ROW SUMMARY

[dilworth]

I am trying to set up a pivot report to summarise the following data:-

Adress Type A Type B Type C Type D
1 Smith Street 2008 2009 2018 2008
2 Smith Street 2018 2008 2010 2008
3 Smith Street 2014 2015 2015 2015
4 Smith Street 2015 2017 2018 2014
5 Smith Street 2012 2014 2015 2014
6 Smith Street 2018 2012 2015 2008
7 Smith Street 2010 2009 2016 2010
8 Smith Street 2009 2008 2019 2015


I want to count the number of adressed for ech Type by year as follows:-

Type A Type B Type C Type D
2008 1 2
2009 1 etc
2010 1 etc
2011 0
2012 1

This works if dropping a single type into the row data but not if more than one type is added-this could be done using the 'countif' function but I want to be able to then drill down to the addresses and to add a total for all of the types.

Any help appreciated.

 

[SkipVought]

Your Source Data ought to look something like this...
[tt]
Adress Val TYP
Smith Street 2008 Type A
Smith Street 2018 Type A
Smith Street 2014 Type A
Smith Street 2015 Type A
Smith Street 2012 Type A
Smith Street 2018 Type A
Smith Street 2010 Type A
Smith Street 2009 Type A
Smith Street 2009 Type B
Smith Street 2008 Type B
Smith Street 2015 Type B
Smith Street 2017 Type B
Smith Street 2014 Type B
Smith Street 2012 Type B
Smith Street 2009 Type B
Smith Street 2008 Type B
Smith Street 2018 Type C
Smith Street 2010 Type C
Smith Street 2015 Type C
Smith Street 2018 Type C
Smith Street 2015 Type C
Smith Street 2015 Type C
Smith Street 2016 Type C
Smith Street 2019 Type C
Smith Street 2008 Type D
Smith Street 2008 Type D
Smith Street 2015 Type D
Smith Street 2014 Type D
Smith Street 2014 Type D
Smith Street 2008 Type D
Smith Street 2010 Type D
Smith Street 2015 Type D
[/tt]
Just pivot off the source data to get...
[tt]
Count of TYP TYP
Val Type A Type B Type C Type D
2008 1 2 3
2009 1 2
2010 1 1 1
2012 1 1
2014 1 1 2
2015 1 1 3 2
2016 1
2017 1
2018 2 2
2019 1
Grand Total 8 8 8 8
[/tt]


Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[dilworth]

Thanks-is there any way to design a pivot table to give these results without formatting the source data this needs to saty the same-have managed to get the same results by using the 'countif' formula which counts the number of instances for each year in the selected range, however I would like to then return the address and postcode where the years 2008-2012 are included in selected columns e.g.

Address Area Type A Type B Type C Type D

1 Smith Street 2008 2009

2 Smith Street 2008

3 smith street

4 smith street 2015 2020

5 smith street 2020 2018

6 smith street 2008

7 smith street 2009 2010

8 smith street 2014

9 smith street

10 smith street 2009

11 smith street

12 smith street 2008

13 smith street

14 smith street

15 smith street



In this example 1 and 2 smith street would be returned as they are in the range of 2008-2012 howver 3 smth street wouldn't as there is no data and 4 wouldn't as the years aren't in the range-hence this is why used pivot table as you can drill down to the data-however if you can sugges another method e.g formula this would be helpful.

 

[AnotherHiggins]

You'll make your life much, much easier in the long run if you store your data properly in the first place.

See Skip's faq68-5287 for help getting your source data in more usable format, then reporting will be a breeze.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.