Results 1 to 4 of 4

Thread: Calculate Subtotal for Repeat Number Only

  1. #1

    Calculate Subtotal for Repeat Number Only



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

    I have a big set of data,where i have a repeat data.I would like to calculate subtotal only for repaeat numbers not for the single numbers.

    For example let say i have data in column A (from row 1 to 10).In row 1 to 4 i have 1234 and row 5 i have 586 and row 6 to 10 i have 894.I would like to calculate the subtotal only for row 1 to 4 and row 6 to 10 not for row 5.

    When i use the subtotal function ,it is calculating the subtotal for row 5 (as stated in the above example) but i don't want to get calculated for row.Pl help me how to do this calculation.

    Thanks in Advance for any help.

    Thanks
    Jay

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    What would the result be for this example?


  3. #3
    Quote Originally Posted by jaku View Post
    I have a big set of data,where i have a repeat data.I would like to calculate subtotal only for repaeat numbers not for the single numbers.

    For example let say i have data in column A (from row 1 to 10).In row 1 to 4 i have 1234 and row 5 i have 586 and row 6 to 10 i have 894.I would like to calculate the subtotal only for row 1 to 4 and row 6 to 10 not for row 5.

    When i use the subtotal function ,it is calculating the subtotal for row 5 (as stated in the above example) but i don't want to get calculated for row.Pl help me how to do this calculation.

    Thanks in Advance for any help.

    Thanks
    Jay
    Hello Jay,
    Assuming that your data range is named "List"

    If you want to sum only the duplicate numbers, try the following:
    =SUM(IF(COUNTIF(List,List)>1,List))

    enter with Ctrl + Shift + Enter, instead of Enter

    The above formula counts all of the duplicate numbers. (i.e. it will count the value 2 three times if the value 2 is repeated three times in the list.)

    On the other hand, if you wish to sum only the duplicates, but sum each duplicated value only once, try the following formula:
    =SUMPRODUCT(IF(FREQUENCY(List,List)>1, List))

    In this case, even if the value 2 is repeated multiple times, it will be included in the summation exactly once.

    Cheers,
    Sajan.

  4. #4
    Conjurer royUK's Avatar
    Join Date
    Mar 2011
    Location
    Derbyshire, UK
    Posts
    155
    Articles
    0
    Excel Version
    most versions
    Maybe a PivotTable. Attach a workbook with some dummy data & required result
    Hope that helps

    Roy

Posting Permissions

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