Thread: summing data in a table if a vertical and horizontal requirement are met

I have a large pivot table, and need to sum data from it when the row above the data table says "Pre" (there will be multiple of these), and the column to the left says "Sales - beer - craft" (this will only occur once). I can't use a conventional sumif because when the pivot table updates it moves the rows the data is on, and the rows being referred to by my formulas start to link to the wrong row.

Does anybody know how to do this?.

You can use SUMIF I think.

Say your table covers column C:H and variable rows. Where column C contains the left side row headers and the data actually starts in column D.... and also that you are entering the "Pre" in row 1 of D:H

Try something like:

=SUMIF($D$1:$H$1,"Pre",INDEX($D:$H,MATCH("Sales - beer - craft",$C:$C,0),0))

so assuming as you said that "Sales - beer - craft" exists once in column C, then that row will be summed where "Pre" flags desired columns to sum.