# Thread: Formula to calculate values > 0 on row 5 on Pivot Table

1. ## 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))

2. 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 A-K yet your formulas refer to cells there. Also there is no pivot table in your example.]

Steve

3. 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

4. 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

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))`
Steve

6. 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

7. 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 7-1 [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,7-1,0,1,7-2)
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

8. Thanks very much for the explanation

Howard

9. Hi Steve

I have a similar Calculation on a Pivot Table, but now need to calculate the ageing 60-90, 90-120 , > 120

It would be appreciated if you could assist me

10. Change the ">60" to the respective "60-90", "90-120", and ">120" as listed in the Pivot table col a.

Steve

11. 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

12. 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("60-90",'Pivot Table'!\$A:\$A,0)-1,0,MATCH("Grand Total",'Pivot Table'!\$A:\$A,0)-MATCH("60-90",'Pivot Table'!\$A:\$A,0),MATCH("Grand Total",'Pivot Table'!4:4,0)-2),">0")))`
The row starts at "60-90" akin to what was done before, but instead of being 1 row high, is the height from the end to the starting row (determined by the Grand total). Like before it is from the Grand total column to column 2. So in your example the range is B6:H8 and it does a SUMIF for ">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

13. Hi Steve

Thanks-this works perfectly

Howard

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•