Ok this is what I have. I have a list of accounts: 100-2, 100-3, 200-2,
200-4,
400-2, 400-3, 400-4, etc. all in one column. Then I have the balances for
each of these accounts in another column. What I am trying to do (with no
success thus far) is create a formula that takes all the accounts that end in
"2" and add them together, all the accounts that end in "3" and add them
together etc. I figured that I would need a formula for each check digit but
I cannot figure out how to get it to work. I have used SUM, IF, CHOOSE, in
all kinds of variations. I am stuck. Is this something I can actually do?
If it is, who wants to be my saviour today? :-)

You might need a helper column
Column A is the account numbers 100-1, 100-2 etc
Column b is =right(a1,1)
column c is the balances
To add all the accounts ending in 1 use =SUMIF(B1:B5,1,C1:C5), changing the
range as required and the '1' to the number of the account you need.

Hope this helps
--
John
MOS Master Instructor Office 2000, 2002 & 2003
Please reply & rate any replies you get

Ice Hockey rules (especially the Wightlink Raiders)


"amour24" wrote:

Ok this is what I have. I have a list of accounts: 100-2, 100-3, 200-2,
200-4,
400-2, 400-3, 400-4, etc. all in one column. Then I have the balances for
each of these accounts in another column. What I am trying to do (with no
success thus far) is create a formula that takes all the accounts that end in
"2" and add them together, all the accounts that end in "3" and add them
together etc. I figured that I would need a formula for each check digit but
I cannot figure out how to get it to work. I have used SUM, IF, CHOOSE, in
all kinds of variations. I am stuck. Is this something I can actually do?
If it is, who wants to be my saviour today? :-)

Let's say all of you accounts are in col A, in rows 2-100, and the values are
in column B

Try

=sumproduct(--(right(a2:a100,1)="2"),b2:b100)


"amour24" wrote:

Ok this is what I have. I have a list of accounts: 100-2, 100-3, 200-2,
200-4,
400-2, 400-3, 400-4, etc. all in one column. Then I have the balances for
each of these accounts in another column. What I am trying to do (with no
success thus far) is create a formula that takes all the accounts that end in
"2" and add them together, all the accounts that end in "3" and add them
together etc. I figured that I would need a formula for each check digit but
I cannot figure out how to get it to work. I have used SUM, IF, CHOOSE, in
all kinds of variations. I am stuck. Is this something I can actually do?
If it is, who wants to be my saviour today? :-)