Counting specific figures based on pivot table data

[Beatrix]

Hi All ,

I need to count number of 4,3,2,1 on a pivot table where it shows latest outturn figure. The calculation should include only the latest data. If the last column is blank or the figure is 0 then it should go and check previuos column until there are some data which is greater than 0. I can manually set CountIfs function to do this calculation but it should be dynamic process as new columns will be added each time when source is updated so I don't know how can I set the formula to make it dynamic for this structure. Which approach is better? Vba or formula?

I attached a sample. Can anyone help me on this please?

Cheers
B.

[Bob Phillips]

Does this work for you?

=COUNTIFS(INDEX($A$13:$Z$100,0,MATCH("Values",$13:$13,0)),"Quartile Position",INDEX($A$13:$Z$100,0,MATCH(LOOKUP("zzz",$13:$13),$13:$13,0)),4)

[Beatrix]

Thanks very much xld ,

that's brilliant but needs to be changed slightly.. The formula doesn't include the previous figure if the latest figure is 0 or blank. Number of 4s should be 2 and number of 3s should be 4. Formula doesn't count the figures in K41, K55, K62

the calculation should be based on the latest figures for Quartile Position which is not blank or not 0. If it is then it should check previous figures. Is it possible to achieve it with this formula?

Also what is zzz in the formula?

Cheers
B.

[Bob Phillips]

In your data, if L41 were 0, K 41 were also 0, but J41 was 4, should that be counted? Or to put it another way, do we count a 4 in any column of Quartile Position? If the answer is yes, if that row contains a 3 and a 4, would they both be counted?

As an example, row 27 has 4,3,3,3. The 3 obviously counts once, but does the 4? Row 34 has 2,3,4,4. Do the 2 and 3 count?

[Beatrix]

Hi again.

We count the last figure which is >0 (at right) in any column of Quartile Position. Answer for row 34 is nope it doesn't count 2 and 3 because the latest figure at right for row 34 is 4 so it counts 4 just once. Please see below:

Number of 4: L34, K41= number of 4 is 2
Number of 3: L20, L27, K55, K62 = number of 3 is 4
Number of 2: no data
Number of 1: no data

I will set the formula in G7,G8,G9,G10 to calculate number of 4,3,2,1 separately.

Does it make sense:think: ?






In your data, if L41 were 0, K 41 were also 0, but J41 was 4, should that be counted? Or to put it another way, do we count a 4 in any column of Quartile Position? If the answer is yes, if that row contains a 3 and a 4, would they both be counted?

As an example, row 27 has 4,3,3,3. The 3 obviously counts once, but does the 4? Row 34 has 2,3,4,4. Do the 2 and 3 count?

[mancubus]

a helper column can be used.

M14 formula

=IF(H14<>"Quartile Position",0,IF(L14>0,L14,IF(K14>0,K14,IF(J14>0,J14,IF(I14>0,I14,0)))))
copy down to M62

G7 formula:

=COUNTIF($M$14:$M$62,4)

G8 formula:

=COUNTIF($M$14:$M$62,3)

G9 formula:

=COUNTIF($M$14:$M$62,2)

G10 formula:

=COUNTIF($M$14:$M$62,1)

[Beatrix]

Thanks for your reply mancubus. It's working now but when the pivot is updated with new columns then the formula in column M needs to be modified. If I am the user it wouldn't be a problem but there are some users without Excel knowledge. What would you suggest to make this structure dynamic?

[Bob Phillips]

This is so specialist, I wouldn't bother trying to get a formula to do it, I would add a UDF.


Public Function GetQuartile(ByVal position As Long) As Long
Dim pt As PivotTable
Dim rng As Range
Dim valuesCol As Long
Dim lastCol As Long
Dim cnt As Long
Dim i As Long, ii As Long


Set pt = ActiveSheet.PivotTables("PivotTable2")
Set rng = pt.TableRange1
valuesCol = Application.Match("Values", rng.Rows(2), 0)
lastCol = rng.Columns.Count
For i = 1 To rng.Rows.Count

If rng.Cells(i, valuesCol).Value = "Quartile Position" Then

For ii = lastCol To valuesCol + 1 Step -1

If rng.Cells(i, ii).Value = position Then

cnt = cnt + 1
Exit For
ElseIf rng.Cells(i, ii).Value <> 0 Then

Exit For
End If
Next ii
End If
Next i

GetQuartile = cnt
End Function


Call it on the worksheet like so

=GetQuartile(4)

[mancubus]

you are welcome.

if i can, i wont let them open the workbook. :)

well. i would first organize a short meeting to train them on the basics of excel, pivot table and formula building, add an instruction worksheet about using the pivot table and formulas.

[mancubus]

@xld;

in case pt name changes, does adding the pt name as an argument to udf sound good?

like this:



Public Function GetQuartile2(ByVal position As Long, PTName As String) As Long

Dim pt As PivotTable
Dim rng As Range
Dim valuesCol As Long
Dim lastCol As Long
Dim cnt As Long
Dim i As Long, ii As Long


Set pt = ActiveSheet.PivotTables(PTName)
Set rng = pt.TableRange1
valuesCol = Application.Match("Values", rng.Rows(2), 0)
lastCol = rng.Columns.Count

For i = 1 To rng.Rows.Count
If rng.Cells(i, valuesCol).Value = "Quartile Position" Then
For ii = lastCol To valuesCol + 1 Step -1
If rng.Cells(i, ii).Value = position Then
cnt = cnt + 1
Exit For
ElseIf rng.Cells(i, ii).Value <> 0 Then
Exit For
End If
Next ii
End If
Next i

GetQuartile2 = cnt


End Function




=GetQuartile2(4,"PivotTable2")

[Bob Phillips]

In principle, I agree, and I did think about that, but as the OP said something about other users not be Excel savvy, I decided not to bother in the end.

[mancubus]

actually, before posting it, i thought a 2 argument udf would be more confusing than with one argument for novice users as well.

then i took into account the possibility of pivot tables created by those novice users. :)

i think, this workbook must be well designed. (so must all workbooks be.)

[Beatrix]

Thank you soooo much:bow: I can't thank you enough :cloud9: this UDF made my day! I appreciate every second you spent.. The deadline is by Thursday so I wouldn't be able to make it dynamic if you didn't help.

@mancubus: thanks very much. I appreciate for your help & suggestion but there is no way you can train people at senior management I am afraid. At least at my work place. :crying:

cHEERS
B.

[mancubus]

you're welcome.

haha.

:rotlaugh:

everybody can be convinced to learn something. :whistle:

the key is picking up the correct method. and i know you ladies can do this better than we guys can. :yes

[Bob Phillips]

I was working with GetPivotData yesterday, and it got me thinking about this and what mancubus said. As I mentioned, I agreed with him in principle, but thought that asking Beatrix's users to know the pivot table name was maybe asking a bit to much. so like GetPivotData, I thought how about if they just have to pass any cell within the pivot table as a parameter to the UDF, that should not be too onerous.

This is what I came up with


Public Function GetQuartile(ByVal cell As Range, ByVal position As Long) As Variant
Dim pt As PivotTable
Dim rng As Range
Dim valuesCol As Long
Dim lastCol As Long
Dim cnt As Long
Dim i As Long, ii As Long

On Error Resume Next
Set pt = cell.PivotTable
On Error GoTo 0

If Not pt Is Nothing Then

Set rng = pt.TableRange1
valuesCol = Application.Match("Values", rng.Rows(2), 0)
lastCol = rng.Columns.Count
For i = 1 To rng.Rows.Count

If rng.Cells(i, valuesCol).Value = "Quartile Position" Then

For ii = lastCol To valuesCol + 1 Step -1

If rng.Cells(i, ii).Value = position Then

cnt = cnt + 1
Exit For
ElseIf rng.Cells(i, ii).Value <> 0 Then

Exit For
End If
Next ii
End If
Next i

GetQuartile = cnt
Else

GetQuartile = CVErr(xlErrRef)
End If
End Function


The worksheet call is then simply =GetQuartile($H$13,4), as I say the $H$13 can be any cell in the pivot.

[mancubus]

much better than pivot table name.
:clap:

users can type in the cell reference $H$13 or just click H13 (or any cell in pivot table) as first argument, then type in 4, 3, 2 or 1 as second argument.

[Bob Phillips]

It also dispenses with the need to refer to a sheet, as the cell passes that implicitly, which is good, especially as I used Activesheet in my code rather than the Caller.Parent.

[Beatrix]

This is just perfect:bow: They even don't need to know what pivot table is. They just click any cell and put a number.Yayyy:cloud9: I need to start picking the right method as mancubus said..didn't think about the UDF at all.