Calculate Subtotal for Repeat Number Only

[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

[NBVC]

What would the result be for this example?

[Sajan]

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.

[royUK]

Maybe a PivotTable. Attach a workbook with some dummy data & required result