I can't think of a short title for this
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? :-) |
I can't think of a short title for this
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? :-) |
I can't think of a short title for this
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? :-) |
All times are GMT +1. The time now is 01:56 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com