1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

sum-up only filtered data (contains different suffixes)

Discussion in 'Ask an Excel Question' started by relio, Oct 19, 2017.

  1. relio

    relio New Member

    Messages:
    8
    hello, this is relio new here, i have problem and uploaded xl-file for reference
    simple daily account data contains figures with suffix needed to sumup the values by the reference of items on left, i tried subtotal with sumproduct and messed up.

    thanx in advance for any help.

    relio

    Attached Files:

  2. David Evans

    David Evans Active Member

    Messages:
    650
    I've modified your worksheet to make your data range an Excel Table - the filtering will result from the Table's features.

    There are some issues with your addition of numbers given that you're mixing grams and Kgs - you can have Excel add kgs as a label after your numbers (suffixes as you refer to them), but it makes sense to define the measure for the numbers and stick to that. You have a number of measures in the qty - units, feet, grams, liters, bundles and cbs ..... if you need to sum different measures, you'll need to add a column to your table to define a measure. Summing different measures will produce no useful answers; counting them may have some insight. On the latter note, your example also appears to be counting 5 items when there are only four.

    Attached Files:

    Last edited: Oct 19, 2017
  3. relio

    relio New Member

    Messages:
    8
    Hi, thanx for response (David), adding another column for suffix is a straight solution, but here boys carelessly put any suffix, so as per bill (some time may have / may not have suffix), i need the solution (summing up values with suffix) which can trim all suffix inside the formula and then sum-up the values (avoiding table feature) becoz sheet has date issue with login time, am trying other ways, if i get the sol. i will share here for further any improvement.

    Attached Files:

  4. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,619
    Hi ,

    See the attached file.

    Narayan

    Attached Files:

    fredieusa and Thomas Kuriakose like this.
  5. rahulshewale1

    rahulshewale1 Active Member

    Messages:
    245
    hii @relio

    See if is ok ?
    Please find the attached solution using power query and Pivot table with Slicer ,


    Regard
    Rahul shewale

    Attached Files:

    Thomas Kuriakose likes this.
  6. relio

    relio New Member

    Messages:
    8
    hi, rahul & to all i saw power query and Pivot table with Slicer is a good and neat way of data representation, but here it needs like "put the formula & get the result", personally i would like to learn more on power query and Pivot table with Slicer. thanx for this. i have uploaded all three responses for others.
    if any solution i get, i will share here, thanx.

    Attached Files:

  7. relio

    relio New Member

    Messages:
    8
    hello, to all, need help on this topic...
  8. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,619
    Hi ,

    Can you explain exactly what problems you face with the 3 solutions which have been offered ?

    Narayan
  9. relio

    relio New Member

    Messages:
    8
    hello,

    1) sol. its not working for other values (its good to avoid table)
    2) sol. i don't want values get separated with suffix, when items are filtered then sum-up of qty will be shown in the top row
    3) sol. working fine, i tried this, data on 1-sheet and result on another but here (others both ways) did not like.
  10. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    1,948
    Hi,

    Sutotal sum-up without values get separated with suffix

    upload_2017-10-31_1-13-31.png

    1] A4 helper, copied down :

    =IF(B4="","",SUBTOTAL(103,B4))

    and, select A4:A23 >> Custom Format Cells, in the type box enter: ;;;

    2] Count formula, B2 enter :

    =SUBTOTAL(103,B4:B23)

    3] Total sum formula, C2 enter :

    =SUMPRODUCT((A4:A23=1)*TEXT(MID(TEXT(MID(C4:C23&"a",1,COLUMN(B:Q)),),1,COLUMN(A: P)),"0.000;;;\0"))

    Regards
    Bosco

    Attached Files:

    Last edited: Oct 30, 2017
  11. relio

    relio New Member

    Messages:
    8
    hi, bosco,
    your sol. saved me, friend, thanx for working on this problem,

    just for curiosity asking, am really sorry, can we solve it without adding a new column becoz as narayan's sol. (separation of suffix & summing values) was a straight way solution with adding a column to data.

    friends am just typing a thought no disregards for other wonderful solutions, i have uploaded all solutions may be useful for others. waiting for reply.
    thanx,
    with regards,
    relio

    Attached Files:

    Thangavel likes this.
  12. Thangavel

    Thangavel Member

    Messages:
    85
    Hello relio,
    Thanks for consolidating different ways to subtotal data.
  13. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    1,948
    The helper additional column can be removed,

    1] Total sum formula, C2 enter :

    =SUMPRODUCT(TEXT(MID(TEXT(MID(C4:C23&"a",1,COLUMN(B:U)),),1,COLUMN(A:T)),"0.000;;;\0")*SUBTOTAL(103,INDIRECT("C"&ROW(4:23))))

    2] See attached revise file

    Regards
    Bosco

    Attached Files:

  14. relio

    relio New Member

    Messages:
    8
    hi, bosco... thanx and sorry for late to respond, i had some issues here but
    ur's 2nd solution worked for me and it resulted correctly till now. if any issues raised i will inform you, again i have uploaded all solution for others.
    thankyou
    with regards
    relio:)

    Attached Files:

  15. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    1,948
    Perhaps……..

    In "Bosco's Solu._2" sheet "Sl no." A4, formula copied down :

    =SUBTOTAL(103,B$4:B4)

    Regards
    Bosco
    Thomas Kuriakose likes this.
  16. relio

    relio New Member

    Messages:
    8
    hi Thangavel D,
    i have uploaded all soln. last solution of bosco's worked for me...

    with regards
    relio:)

Share This Page