MrExcel Publishing
Your One Stop for Excel Tips & Solutions

redundant data


Posted by Colin on December 21, 2001 4:33 AM

I posted this problem earlier this week but I don't think I stated my problem correctly.
I have already looked at the weekly tips on this subject and cannot find a solution.

My problem is that I have a huge file and I wish to get rid of some data and sum it in a column.

I have a list of data and when a department(each have a different number) has more than one account of the same number sum up the values in another column into one. The original rows that these accounts are in can be deleted.

for example:
Before-
111 543 $54,764
111 543 $45,765
111 543 $32,543

After-
111 543 $133,072

The problem is that not all accounts need to be sumed just some specific ones.

Any help is appreciated.

Thanks


Posted by Shams on December 21, 2001 6:18 AM

You can use SUMIF() Function

Assume, the account numbers are in Range A1:A10
and amount in Range B1:B10

In Cell A15 type the following function to get the total of all the amounts whose account number is 111 543

=SUMIF(A1:A10,"111 543",B1:B10)


Regards,
Shams

Posted by Shams on December 21, 2001 6:18 AM

You can use SUMIF() Function

Assume, the account numbers are in Range A1:A10
and amount in Range B1:B10

In Cell A15 type the following function to get the total of all the amounts whose account number is 111 543

=SUMIF(A1:A10,"111 543",B1:B10)


Regards,
Shams

Posted by Scott on December 21, 2001 9:09 AM

You can also try the subtotal function (DATA-SUBTOTALS) and subtoal by Acct#. This will not delete columns, but it will give you a cleaner view.