USING WILDCARDS IN GETPIVOTDATA()

Question

Good Morning,

I'd have a question regarding getpivotdata()

I have the following pivot table:

| |LIGURIA|PIEMONTE|LOMBARDIA|TOTALS |

|GIOVANNI | 5 | 10 | 6 | 21 |

|LUIGI | 6 | 8 | 9 | 23 |

|MARCO | 10 | 15 | 18 | 43 |

|TOTALS | 21 | 33 | 33 | 87 |

I'd want to retrieve the sum of LUIGI in LIGURIA AND LOMBARDIA

It's possible to use wildcards in get pivot data to get the sum of values for LUIGI for the columns L*?

to choose the sums i use reference to a cell :

for example i have g1: "LUIGI" and G2 (for example) LIGURIA. If iìd want to use a reference for L* how should i do?

G2= "L*" or g2="L" and getinfo data argument equal to concatenate(G2,"*")?

Answer 1

Thank you , sadly this doesnt solve my problem.

i've found a workaround not too bad after all.

I've written the desired voices in G2, separated by "|" and using xmlfilter, index and multiple sums i've obtained something similar to my objective.

the key was to find a way to transform the content of a cell into an array inside a formula ^_^

Thanks

Answer 2

GETPIVOTDATA does not support wildcards.

You might enter LIGURIA in G2 and LOMBARDIA in G3. You can then use a formula like this:

=SUM(GETPIVOTDATA("Valore", PivotTableCell, "Nome", G1, "Regione", G2:G3))

Substitute the field names and a cell in your pivot table.