Count List items, (Index, Match Vlookup) help

[ScottEmberson]

Hi Everyone
I currently have a pivot table with following information:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Row
[/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Area Path
[/TD]
[TD]Count of ID
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]\Bails and Hearings\Bails
[/TD]
[TD]20
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]\Bails and Hearings\Court Hearing
[/TD]
[TD]9
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]\Case Management\Case History
[/TD]
[TD]3
[/TD]
[/TR]
</tbody>[/TABLE]

I need a formula to count all the items in the 1st level of the area path e.g. \Bails and Hearings. So from my example in another table it will say \Bails and Hearings total of 29 and \Case Management total 3.


Hope this makes sense


Any help would be much appreciated

 

[Andrew Poulsom]

Example:

Excel 2010
	A	B	C	D	E
1	Area Path	Count of ID		\Bails and Hearings	29
2	\Bails and Hearings\Bails	20		\Case Management	3
3	\Bails and Hearings\Court Hearing	9
4	\Case Management\Case History	3
Sheet1

Cell Formulas
Range	Formula
E1	=SUMIF(A:A,D1&"*",B:B)

 

[ScottEmberson]

Thank you, works like a treat