How to convert Google Spreadsheets pivot table to Excel?

Question

I have a Google Spreadsheet with the following data:

I have copied the data to Excel (office 365):

You can access the data here: https://docs.google.com/spreadsheets/d/1Hn8wdcfNlMLNZNgDnFAJfYJ2g1OIW3rKGvoacHU5xrQ/edit?usp=sharing Or:

Company	Field ID	Field area (ha)	Crop in 2024
A	1	62	B
B	2	13	Alfalfa
C	3	19	N
B	4	72	Sz
B	5	48	Sz
C	6	69	Alfalfa
A	7	74	K
B	8	4	Á
A	9	25	Alfalfa
A	10	80	B
C	11	4	N
C	12	21	K
A	13	63	K
A	12	18	Alfalfa
B	15	76	B

I would like to calculate the sum of the areas where the "Crop in 2024" is not alfalfa. For this purpose, I use the formula: =SUM(FILTER('Field area (ha)'; 'Crop in 2024' <> "Alfalfa")). The name of the column where the calculation takes place is "Szántó".

I have a working solution in Google Spreadsheets:

However, when I try to copy my solution to Excel (office 365) it does not work:

Note: If I change the "Summarize by" value in Google Spreadsheets from Custom (Egyéni) to SUM, it does not work in Google Spreadsheets. It produces the same output as Excel does.

How could I convert my pivot table to Excel? If possible, I would like to use this formula.

Answer 1

Select all of the data and, on the Insert tab of the ribbon, click on Pivot Table -> From Table/Range

On the window that pops up next, ensure that the highlighted box is checked

Having clicked OK on the previous pop up, the Power Pivot tab of the ribbon should be selected, and then Measures -> New Measure

On the window that pops up, a value should be entered in the Measure name field

(the Table Name field above is populated with Range because the selected data were a regular (un-named) Range/Table)

and this

=SUMX(FILTER(Range,Range[Crop in 2024]<>"Alfalfa"),Range[Field area (ha)])

should be entered in the Formula box.

Having clicked OK on the previous window, the new measure should automatically be added to the Values panel

Answer 2

If you don't necessarily need a pivot, this would work:

=LET(u,UNIQUE(FILTER(A2:A16,D2:D16<>"Alfalfa")),
     s,SUMIFS(C2:C16,A2:A16,u,D2:D16,"<>"&"Alfalfa"),
HSTACK(u,s))

or

=LET(range,   A2:D16,
     exclude, {"Alfalfa","B"},
     company, INDEX(range,,1),
     area,    INDEX(range,,3),
     crop,    INDEX(range,,4),
unique_comp,  UNIQUE(FILTER(company,ISNA(XMATCH(crop,exclude)))),
sum_area,     MMULT(N(TOROW(company)=unique_comp),area*ISNA(XMATCH(crop,exclude))),
HSTACK(unique_comp,sum_area))

Where you can change the exclude and range parameter to your needs.