Nesting Countif and getpivotdata

[Guruprasad Rao]

Hi,

I am having problems with my report generation, earlier we were using multiple pivots to build a single report, which was time consuming. I have sorted out most of the problems by building a single pivot and using getpivotdata. But one of the few problems I have now is with getting the count of days from the pivot.

Below is the extract from the pivot:

		Values
Class	Date	Result	Count of Date
2B -Sc - G1	21/07/2013	29.2	4
	22/07/2013	23.6	4
	23/07/2013	24.9	4
	24/07/2013	35.2	5
	25/07/2013	27.8	4
	26/07/2013	25.3	4
	27/07/2013	27.4	4
	28/07/2013	32.4	5
	29/07/2013	26.1	4
	30/07/2013	37.7	5
	31/07/2013	28.9	4
2B - Sc -G1 Total		318.5	47

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

If I use getpivotdata and use "Count of Date" as basis i would get 47, (No of students is not constant this varies from 3 to 12).

I want the resultant to be 11 for Class - 2B - Sc - G1 in my report.

I hope this forum would help me in resolving this problem.

Thanks
G Rao

 

[RoryA]

Hi, and welcome to the forum.

Which version of Excel are you using?

 

[Guruprasad Rao]

Hi Rory,

I am using 2007

 

[RoryA]

Shame. IN that case you'll need a formula like this:
=LOOKUP(2,1/(A1:A100=F2&" Total"),ROW(A1:A100))-MATCH(F2,A1:A100,0)
where F2 contains the Class name. You may need to adjust the ranges - just make them bigger than your table will ever be, but preferably not entire columns.

 

[Guruprasad Rao]

I will check and update that,

What would be the way in case I use 2010, I have to on my laptop.

 

[RoryA]

With 2010 you can choose to repeat the Class item label and then just use COUNTIF.