Results 1 to 2 of 2

Thread: Advanced Filter and sum

  1. #1

    Advanced Filter and sum



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have a list of data and need to sort it based on field NUMBER and NAME. Once done, I need to sum it according to the filter. Anyone has a better idea on how to do it? As of now, I will filter field Number then field Name; then, I will create 2 additional blank column at the end of each filter and manually sum it. Reason being I created these additional columns is to make sure it will not mix up the sum amount as I need the sum data to be parked in another sheet. Example as per attached... Please help as it took me ages to do this activity....
    Number Name total sum total
    2701 Actual June'14 Project A 8,952,342.00
    2701 Actual June'14 Project A 23423
    2701 Actual June'14 Project B 23,424,234.00
    2701 Actual June'14 Project B 342,424.00
    2701 Actual June'14 Project B 2,342,342.00
    2701 Actual June'14 Project C -453,534.00
    2701 Actual June'14 Project C -43,534.00
    2701 Actual June'14 Project D -45.00
    34,587,652.00
    2701 ACCRUAL June'14 Project A -567,657.00
    2701 ACCRUAL June'14 Project B -54,654,646.00
    2701 ACCRUAL June'14 Project C -2,050,800.00
    2701 ACCRUAL June'14 Project D -1,720,000.00
    -58,993,103.00
    2704 Credit June'14 -7,892,400.00
    2704 Credit June'14 6,905,850.00
    -986,550.00
    2704 Actual July'14 Project A 2,342,424.00
    2704 Actual July'14 Project A 34,242,342.00
    2704 Actual July'14 Project A 2,342,424.00
    2704 Actual July'14 Project b 23,424,234.00
    2704 Actual July'14 Project b 23,424,234.00
    2704 Actual July'14 Project b 342,424.00
    2704 Actual July'14 Project c 342,424.00
    2704 Actual July'14 Project c 342,424.00
    2704 Actual July'14 Project c -453,534.00
    86,349,396.00
    2701 ACCRUAL July'14 Project A -167,657.00
    2701 ACCRUAL July'14 Project b -32,654,646.00
    2701 ACCRUAL July'14 Project c -1,050,800.00
    -33,873,103.00

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,429
    Articles
    0
    Excel Version
    Excel 2016
    Assume you have those items in columns A:B, try this formula in C2:

    =IF(OR(A3="",A2&"_"&TRIM(LEFT(SUBSTITUTE(B2," ",REPT(" ",100)),200))<>A3&"_"&TRIM(LEFT(SUBSTITUTE(B3," ",REPT(" ",100)),200))),SUMIFS(C:C,A:A,A2,B:B,TRIM(LEFT(SUBSTITUTE(B2," ",REPT(" ",100)),200))&"*"),"")

    copied down.

    This will show the sum on the same row as the last occurance of Unique Number/Name(first 2 words) combination.


Tags for this Thread

Posting Permissions

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