## Pivot table as formula - possible - how

### Pivot table as formula - possible - how

Hope I can explain what I want
Suppose I have the following columns (below). (Column1 contains text and column2 contains numbers)

I want to sum all 1111, 111-111, 2222, 123 (and create a pivot table using a formula)
Is it possible with OO?

 Col-A Name 1111111-11122222222111-1111111123

 Col-B Number 51214328137

Columns C and D show the result as follows .:
1111 = 18
111-111 = 40
2222 = 17
123 = 7
OOo 4.1.X on Windows XP, Win7, 10
Albireo

Posts: 59
Joined: Wed Apr 15, 2009 12:05 pm

### Re: Pivot table as formula - possible - how

A pivot table works without any formulas. https://wiki.openoffice.org/wiki/Docume ... /DataPilot
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4

Villeroy
Volunteer

Posts: 28546
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

### Re: Pivot table as formula - possible - how

Yes - but!
Pivot tables are a powerful tool.
If the conditions are changed, it is possible to change the pivot table and layout.

However (when I was using pivot tables - log time ago) there were some limitations.
- No empty rows / columns in the table
- If the default values change, the pivot table is not updated.
(were the ones I spontaneously remember - there are certainly more limitations)
Would the table in the Calc be sorted?
Are these restrictions or any others remaining?

But I just want a calculation (no formatting) - similar to CALCULATEIF ...
Ie sum all values for each alternative, using a formula - Is it possible in OO?
OOo 4.1.X on Windows XP, Win7, 10
Albireo

Posts: 59
Joined: Wed Apr 15, 2009 12:05 pm

### Re: Pivot table as formula - possible - how

Automatic update for pivots could turn out to be a nightmare because after every edit you would have to wait for the pivot to update. It's a whole generated table. If you put the pivot on a separate sheet, a simple macro can do the update when the sheet is activated so you never see an old pivot.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4

Villeroy
Volunteer

Posts: 28546
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

### Re: Pivot table as formula - possible - how

If you want parts of the functionality of pivot tables or enhancements to it you will have to do a lot of programming, imo. The tool itself will not work without a destination, and you need to provide the required space in a sheet for it.

If it is about refreshing a pivot table similar to cells containing formulas under AutoCalculate: A user function getting passed the source range data and info about the source range (location) itself can identify the pivot table(s) needing a refresh and trigger it. Efficiency of your sheets under AutoCalculate may significantly go down. (Have a lot of fun!)
On Windows 10: LibreOffice 7.0 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München

Lupp
Volunteer

Posts: 2914
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

### Re: Pivot table as formula - possible - how

OK!

Thought there was some "easy" way / formula to accomplish this.
1.) Filter out unique content in a column
2.) Find and add the number in the next column

Is it possible to manually update the pivot table?
OOo 4.1.X on Windows XP, Win7, 10
Albireo

Posts: 59
Joined: Wed Apr 15, 2009 12:05 pm

### Re: Pivot table as formula - possible - how

Albireo wrote:Thought there was some "easy" way / formula to accomplish this.
1.) Filter out unique content in a column
2.) Find and add the number in the next column
Calc can perform those operations with formulas. Whether they are "easy" depends on your skill level. If you don't understand the second tutorial, stick with pivot tables.

Don't even think about attempting this project until you are thoroughly familiar with this material:
[Tutorial] Ten concepts that every Calc user should know

#1 is accomplished by writing filter formulas and using "not equal to any previous entry" as the filter criteria. You can sort the filtered results if you like, though this requires more formulas. Be advised that you will want to use auxillary columns for these calculations.
[Tutorial] Sorting and Filtering data with formulas

#2 is accomplished with the SUMPRODUCT formula once the filtered values are obtained from #1:
[Tutorial] The SUMPRODUCT function

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).

MrProgrammer
Moderator

Posts: 3961
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

### Re: Pivot table as formula - possible - how

Albireo wrote:OK!

Thought there was some "easy" way / formula to accomplish this.

Why would pivot tables exist then?
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4

Villeroy
Volunteer

Posts: 28546
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany