Results 1 to 13 of 13
  1. #1
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,023
    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))
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    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 A-K yet your formulas refer to cells there. Also there is no pivot table in your example.]

    Steve

  3. #3
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,023
    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

  4. #4
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,023
    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

  5. #5
    WS Lounge VIP sdckapr's Avatar
    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))
    Steve

  6. #6
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,023
    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

  7. #7
    WS Lounge VIP sdckapr's Avatar
    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 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. #8
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,023
    Thanks
    63
    Thanked 2 Times in 2 Posts
    Thanks very much for the explanation

    Howard

  9. #9
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,023
    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 60-90, 90-120 , > 120

    It would be appreciated if you could assist me
    Attached Files Attached Files

  10. #10
    WS Lounge VIP sdckapr's Avatar
    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 "60-90", "90-120", and ">120" as listed in the Pivot table col a.

    Steve

  11. #11
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,023
    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
    Attached Files Attached Files

  12. #12
    WS Lounge VIP sdckapr's Avatar
    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("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. #13
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,023
    Thanks
    63
    Thanked 2 Times in 2 Posts
    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
  •