Excel formulas issue

[airborne spoon]

I am generally pretty good with excel and making formulas, but i encountered an issue today I cant seem to figure out where I'm going wrong.

 

I made the basic formula =SUM(G2:G11) and =SUM(G12:G29) for my totals and when I change the sort from A to Z, to Z to A it changes the totals because the formula is based on the cells not the data in that line item.

How do I get it to keep the right data when I change the sort order? You can see in the pics how changing the sort filter made the data change pretty drastically.

 

[NineEyeRon]

Make it a table first?

[airborne spoon]

6 minutes ago, NineEyeRon said:

Make it a table first?

Nope, that had no effect

[Avocado Diaboli]

You'll probably want to use a different formula that takes the values in column B into account. I hope I translate it properly here, since Excel actually localizes the names of functions to other languages and mine is set to German. But in your case, you can use the following formula in these cells:

 

Cell J30 =SUMIFS(G2:G29; B2:B29; I30)

Cell J31 =SUMIFS(G2:G29; B2:B29; I31)

 

The SUMIFS function only ads up numeric values if the value of a different range matches a specific search term. In this case G2:G29 is the range where all the numeric values are, B2:B29 is the range of all the possible criteria and I30 and I31 contain the value that you're actually filtering by.

[IkeaGnome]

Table would help here. You could then have it reference it's self in the same line. Here's one I use for inventory management.

=IFERROR(IF(SUM(InventoryList[@[ON HAND]],InventoryList[@[QTY ORDERED]])<InventoryList[@MINIMUM],1,0),0)

I can resort my table as I please and the formula stays with that line. 

[Avocado Diaboli]

7 hours ago, IkeaGnome said:

Table would help here.

A table would only help in the sense that the formula would always sum up the entire column of the table, instead of the hard-coded range in my example. But you'd still need something like SUMIFS or a pivot table to do the calculation @airborne spoon is after. Your example would work just as fine without a table because all the data is contained on the same line. They're trying to do calculations on data across lines, which breaks down if you don't use something that tells Excel only to focus on certain values if you also intend to resort things.

 

 

Speaking of pivot tables, here's how you'd solve this problem with one. First, as the others have mentioned, define your data range as a table. Get in the habit of always doing that, it makes a lot of stuff easier, even just sorting and filtering. Make sure the currently selected cell is within the table, then go to Insert -> PivotTable (on the far left of the ribbon). If the selected cell was inside the table, it'll automatically detect the table you want to use as a source for your pivot table, like this. Simply confirm this with OK.

 

You should now get a new worksheet that looks something like this.

 

In the pivot table panel on the right side, click and drag "Region" into the row field and "Total" into the value field, like this:

 

Pivot tables automatically default to sum up numeric values. If you want to do something else with them, double click on the title of the column you want to edit (in my example, that would be the cell labelled as "Summe von Total"), which allows you to change the calculation an also the number format.

 

Keep in mind though that this pivot table solution, unlike the formula I mentioned above, doesn't auto update the values in the pivot table if you change something in the data table. To update it, go to the ribbon, Data -> Refresh All. That way, all the tables get updated.