Search code examples
excelexcel-formulapivotpivot-tablepowerpivot

Use Excel pivot table instead of SUMIFS

I have a fairly simple Excel table. Currently, I am using SUMIFS to get my results.

I made the pictures in Google spreadsheets, so the formulas are displayed in English, however, in reality, I use MS Excel.

Solution with sumifs

My current solution with Pivot tables is using 4 different Pivot tables. I need to combine these 4 Pivots into only 1. I know how to use calculated fields, etc. but I still cannot figure out how to use only 1 pivot table.

Solution with 4 pivots

The first pivot table is using 1 filter (crop 2024), the remaining 3 pivots are using 2 filters (crop 2024, [GS/AM/MV]).

Ultimately I would need the percentages to be calculated in the pivot table itself, but I know how to do that with calculated fields. Is my task possible with pivot tables?

EDIT: Example sheet: https://docs.google.com/spreadsheets/d/1Hn8wdcfNlMLNZNgDnFAJfYJ2g1OIW3rKGvoacHU5xrQ/edit?usp=sharing

Solution

  • It is possible to use pivot tables:

    the key is to combine filter and sum:

    =sum(filter('Field area (ha)',('Crop in 2024'<>"Alfalfa")))

    and to make sure the calculated field is not set to summarize by sum.

    example sheet: https://docs.google.com/spreadsheets/d/1D1zmLJckwJynspTs1z-cv83DziOLxedxcnkZNfZuFpg/edit?usp=sharing

    unfortunately, i wasn't able to grasp the second set of sumifs, but I guess with the formula it should be easy to implement it.