# Thread: Calculate Subtotal for Repeat Number Only

1. ## Calculate Subtotal for Repeat Number Only

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  Reply With Quote

2. What would the result be for this example?  Reply With Quote

3. Originally Posted by jaku 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.  Reply With Quote

4. Maybe a PivotTable. Attach a workbook with some dummy data & required result  Reply With Quote

#### Posting Permissions

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