Replace with subtotals
 

I have Account codes in column A (about 5 per account #), all which have
different values in C. I need to subtotal Column C based on account numbers.
The problem is, is that I need the spreadsheet to display the total for each
account number, instead of all of the entries. I can't hide anything either
because the spreadsheet has to be imported into another software. Thanks

Hi!

Can you post a sample of your data?

Biff

-----Original Message-----
I have Account codes in column A (about 5 per account #),
all which have
different values in C. I need to subtotal Column C based
on account numbers.
The problem is, is that I need the spreadsheet to
display the total for each
account number, instead of all of the entries. I can't
hide anything either
because the spreadsheet has to be imported into another
software. Thanks
.

Here is a sample. Thanks so much for your time. For example, I need all
the 1000-0000 and 1050-000 in one line with the total. Column B should take
the title of the first entry.

1000-0000 Cash - Checking (65,000)
1000-0000 Cash Checking (55,000)
1000-0000 Cash (78,000)
1000-0000 Cash - Checking (3,000)
1000-0000 Cash/Checking (20,000)
1000-0000 Cash (6,000)
1000-0001 Cash - Checking 17,000
1000-0001 Cash - 109,000
1000-1104 Cash Checking 153,000
1000-2032 Cash - Checking 15,000
1000-2033 Cash - Checking 100,000
1010-0000 Cash - (75,000)
1020-0000 Cash - 10,000
1050-0000 Cash - (200,000)
1050-0000 Gift (81,000)
1050-0000 Gift (111,000)

Thanks again.
"Biff" wrote:

Hi!

Can you post a sample of your data?

Biff

-----Original Message-----
I have Account codes in column A (about 5 per account #),
all which have
different values in C. I need to subtotal Column C based
on account numbers.
The problem is, is that I need the spreadsheet to
display the total for each
account number, instead of all of the entries. I can't
hide anything either
because the spreadsheet has to be imported into another
software. Thanks
.

Hi!

If the account numbers are always 4 characters long:

=SUMPRODUCT((--LEFT(A1:A16,4)=1000)*C1:C16)

OR, use a cell to hold the account number:

G1 = 1000

=SUMPRODUCT((--LEFT(A1:A16,4)=G1)*C1:C16)

If the account numbers may be of different lengths:

=SUMPRODUCT((--LEFT(A1:A16,FIND("-",A1:A16)-1)=1000xx)
*C1:C16)

Biff

-----Original Message-----
Here is a sample. Thanks so much for your time. For
example, I need all
the 1000-0000 and 1050-000 in one line with the total.
Column B should take
the title of the first entry.

1000-0000 Cash - Checking (65,000)
1000-0000 Cash Checking (55,000)
1000-0000 Cash (78,000)
1000-0000 Cash - Checking (3,000)
1000-0000 Cash/Checking (20,000)
1000-0000 Cash (6,000)
1000-0001 Cash - Checking 17,000
1000-0001 Cash - 109,000
1000-1104 Cash Checking 153,000
1000-2032 Cash - Checking 15,000
1000-2033 Cash - Checking 100,000
1010-0000 Cash - (75,000)
1020-0000 Cash - 10,000
1050-0000 Cash - (200,000)
1050-0000 Gift (81,000)
1050-0000 Gift (111,000)

Thanks again.
"Biff" wrote:

Hi!

Can you post a sample of your data?

Biff

-----Original Message-----
I have Account codes in column A (about 5 per account
#),
all which have
different values in C. I need to subtotal Column C
based
on account numbers.
The problem is, is that I need the spreadsheet to
display the total for each
account number, instead of all of the entries. I
can't
hide anything either
because the spreadsheet has to be imported into
another
software. Thanks
.


.