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:

[TABLE="width: 362"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD="align: left"]Values[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]Class[/TD]
[TD="align: left"]Date[/TD]
[TD="align: left"]Result
[/TD]
[TD="align: left"]Count of Date[/TD]
[/TR]
[TR]
[TD="align: left"]2B -Sc - G1
[/TD]
[TD="align: right"] 21/07/2013[/TD]
[TD="align: right"]29.2[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"] 22/07/2013[/TD]
[TD="align: right"]23.6[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"] 23/07/2013[/TD]
[TD="align: right"]24.9[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"] 24/07/2013[/TD]
[TD="align: right"]35.2[/TD]
[TD="align: right"]5
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"] 25/07/2013[/TD]
[TD="align: right"]27.8[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"] 26/07/2013[/TD]
[TD="align: right"]25.3[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"] 27/07/2013[/TD]
[TD="align: right"]27.4[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"] 28/07/2013[/TD]
[TD="align: right"]32.4[/TD]
[TD="align: right"]5
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"] 29/07/2013[/TD]
[TD="align: right"]26.1[/TD]
[TD="align: right"]4
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"] 30/07/2013[/TD]
[TD="align: right"]37.7[/TD]
[TD="align: right"]5
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"] 31/07/2013[/TD]
[TD="align: right"]28.9[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: left"]2B - Sc -G1 Total
[/TD]
[TD][/TD]
[TD="align: right"]318.5[/TD]
[TD="align: right"]47
[/TD]
[/TR]
</tbody>[/TABLE]

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.