Count repeated vaule and total sum

**dpatel** · 08-05-2010, 08:07 AM

Hello,

I have got more than 10,000 repeated value in a one column with other data.
Example workbook attached.
On attachement, "Sheet1" contains all the data and result should come on "Sheet2" (see on attachement).

Basically, i would like to make formula that countif(columnA)>1 then display total sum based on repeated vaules in column A.

Thanks in advance!

**NBVC** · 08-05-2010, 08:14 AM

Try a Pivot table. See attached

Here is some help on Pivot Tables:

http://peltiertech.com/Excel/Pivots/pivotstart.htm

**dpatel** · 08-05-2010, 08:45 AM

Thanks for the reply.
I have already tried with Pivot Table but the problem is if any value have been change then it won't reflect on Pivot table that's the reason i was thinking to have reference formula to get update total sum.

How can run a formula instead of Pivot Table?

**NBVC** · 08-05-2010, 08:55 AM

You can refresh the Pivot table after making changes in Sheet1....

But.... if you want formulas, then If you create a column of unique Departments in Sheet2, column A starting in A2, then in B2:

=SUMIF(Sheet1!$A:$A,$A2,Sheet1!B:B)

copied down and across.

To create a unique list in Column A of Sheet2, start by ensuring a title similar to title in column A of Sheet1 is in A1 of Sheet2, then while in Sheet2, go to Data|Filter|Advanced Filter... click OK.

Then for the list range, select column A range of Sheet1.

Select Copy to another location and select A1 of current Sheet.

Then select Unique Records Only

and click Ok.