Results 1 to 13 of 13

20140814, 04:28 #1
 Join Date
 Feb 2008
 Posts
 1,009
 Thanks
 63
 Thanked 2 Times in 2 Posts
Formula to calculate values > 0 on row 5 on Pivot Table
I am using the following formula on Pivot table Row 5 where the items are 60 days & over
and the value > 0
=SUMIF(B7:FV7,">0",B7:FV7)
However if the data changes there will be more or less rows in the Pivot Table. I would like to compute the postive values in row 5 on the Pivot table up to before the column where Grand Total appears
I tried the folowing formula, but it comes up with #name?
=IF(ISBLANK(A4),"",IF(A4="Grand Total",MAX(A4),SUMIF(E7:KK7,">0",E7:KK7)))SUM(OFFSET(OFFSET(Grand_Total,,4,+COLUMN()),,,,2))

Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!
+ Get this BONUS — free!
Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual  Subscribe and download Chapter 1 for free!

20140814, 06:40 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 341 Times in 334 Posts
Do you have a range named "Grand_Total"? That is probably what gives the #name error.
[Your formulas don't seem appropriate to your example. There is nothing in Cols AK yet your formulas refer to cells there. Also there is no pivot table in your example.]
Steve

20140814, 07:22 #3
 Join Date
 Feb 2008
 Posts
 1,009
 Thanks
 63
 Thanked 2 Times in 2 Posts
Hi Steve
The data attached was sample data from the Pivot Table. I have named the Grand Total in the final column and have got this formula to work
=IF(ISBLANK(A4),"",IF(A4="Grand Total",MAX(A4),SUMIF(B7:FV7,">0",B7:FV7)))SUM(OFFSET(OFFSET(Grand_Total,,4,+COLUMN()),,,,2))
I would like to know how this this formula can be amended so as to take into account if the columns increase or decrease in number for EG if the last column FW changes was updated to KW or BW etc

20140814, 09:46 #4
 Join Date
 Feb 2008
 Posts
 1,009
 Thanks
 63
 Thanked 2 Times in 2 Posts
Hi Steve
See example of Pivot Table attached with Formula on Pivot Sheet
It would be appreciated if you would amend the formula to accomate a change in the number of columns
I would like to compute the values where the ageing > 60 days and where the value > 0
Your assistance is most appreciated

20140815, 11:12 #5
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 341 Times in 334 Posts
How about this:
Code:=IF(ISBLANK(A4),"",IF(A4="Grand Total",MAX(A4),SUMIF(OFFSET('Pivot Table'!$B$1,MATCH(">60",'Pivot Table'!$A:$A,0)1,0,1,MATCH("Grand Total",'Pivot Table'!$4:$4,0)2),">0",OFFSET('Pivot Table'!$B$1,MATCH(">60",'Pivot Table'!$A:$A,0)1,0,1,MATCH("Grand Total",'Pivot Table'!$4:$4,0)2))))SUM(OFFSET(OFFSET(Grand_Total,,3+COLUMN()),,,,2))

20140815, 12:12 #6
 Join Date
 Feb 2008
 Posts
 1,009
 Thanks
 63
 Thanked 2 Times in 2 Posts
Hi Steve
This is a brilliant formula. Please explain to me how this works, I am familiar with sumif and offset, but do not understand this formula which give me the correct result
Howard

20140815, 12:33 #7
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 341 Times in 334 Posts
It is your formula, with my addition to give you the range desired. That part is:
OFFSET('Pivot Table'!$B$1,MATCH(">60",'Pivot Table'!$A:$A,0)1,0,1,MATCH("Grand Total",'Pivot Table'!$4:$4,0)2)
Offset defines a range.
The first parameter ['Pivot Table'!$B$1] is the "reference", the location to start the definition from.
The 2nd parameter is the "row offset" from the reference
MATCH(">60",'Pivot Table'!$A:$A,0) gives you the row number where ">60" is located. The "1" is because offset is zero based. [In your example the Match gives row 7, so the offset at this point is 71 [6 rows] from B1 which is B7
The 3rd parameter is the "Column Offset" which is 0 so the the start of the range is B7 in your example [If desired you could start at A1 and use 1 as the column offset, it gets you to the same place]
The 4th parameter is the "height" which is 1 so it stick with Just row 7 in this example.
The 5th parameter is the width: the number of columns in the range. MATCH("Grand Total",'Pivot Table'!$4:$4,0) determines the column number that contains the "Grand Total" (in your example Column G = 7). It then subtracts 2. The 2 columns being subtracted are Col A and the column with the grand total, so the range is 5 cols wide, which means the offset becomes in your example:
OFFSET('Pivot Table'!$B$1,MATCH(">60",'Pivot Table'!$A:$A,0)1,0,1,MATCH("Grand Total",'Pivot Table'!$4:$4,0)2)
OFFSET('Pivot Table'!$B$1,71,0,1,72)
OFFSET('Pivot Table'!$B$1,6,0,1,5)
So it says:
Start at B1, go 6 rows down, 0 cols to left, be 1 row high and 5 cols wide. That defines the range as:
B7:F7
As the pivot changes, the rows down will adjust for the row containing ">60" and the width will adjust for the column with "Grand Total"
Steve
Steve

20140815, 13:01 #8
 Join Date
 Feb 2008
 Posts
 1,009
 Thanks
 63
 Thanked 2 Times in 2 Posts
Thanks very much for the explanation
Howard

20140818, 02:17 #9
 Join Date
 Feb 2008
 Posts
 1,009
 Thanks
 63
 Thanked 2 Times in 2 Posts
Hi Steve
I have a similar Calculation on a Pivot Table, but now need to calculate the ageing 6090, 90120 , > 120
It would be appreciated if you could assist me

20140818, 06:15 #10
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 341 Times in 334 Posts
Change the ">60" to the respective "6090", "90120", and ">120" as listed in the Pivot table col a.
Steve

20140818, 06:44 #11
 Join Date
 Feb 2008
 Posts
 1,009
 Thanks
 63
 Thanked 2 Times in 2 Posts
Have Steve Have done so, but still don't get correct solution
See attached file with my Formula in B2, where I am trying to compute the values greater than 60 days where the values are > 0 and excluding the grand total

20140818, 09:01 #12
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 341 Times in 334 Posts
I don't understand your use of the Grand_Total named range. In the file in Aug 14, it was in cell FW$ so I assumed it served some purpose unrelated to the pivot table. But in this one the name is part of the pivot table. I now suspect it was your attempt to ignore the Grand total calcs. That is not needed in my system.
Does this do what you want?
Code:=IF(ISBLANK(A4),"",IF(A4="Grand Total",MAX(A4),SUMIF(OFFSET('Pivot Table'!$B$1,MATCH("6090",'Pivot Table'!$A:$A,0)1,0,MATCH("Grand Total",'Pivot Table'!$A:$A,0)MATCH("6090",'Pivot Table'!$A:$A,0),MATCH("Grand Total",'Pivot Table'!4:4,0)2),">0")))
I left the "IF(ISBLANK(A4),"",IF(A4="Grand Total",MAX(A4)," per your formula, it has nothing to do with the SUMIF.
Steve

20140818, 09:18 #13
 Join Date
 Feb 2008
 Posts
 1,009
 Thanks
 63
 Thanked 2 Times in 2 Posts
Hi Steve
Thanksthis works perfectly
Howard