Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF using criteria from a list
This one seems easy but I can't figure it out.
A B 1 Acct Amt 2 100 54 3 101 76 4 102 84 5 103 67 6 104 99 7 105 42 8 106 0 9 107 81 How can I take the total sum of accounts (for example) 102,104 & 107. So I am looking for an answer of 84 + 99 + 81 = 264. I will normally use around 15 - 20 accounts. Do I need a separate SUMIF formula for each account, or can I set up one and input a list of accounts for the criteria?? Thanks in advance |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF using criteria from a list
One way of doing it would be to list the accounts you want in, for
example, F2:F15, leaving cells blank if not required. Then in G2 you can put this formula: =IF(F2="","",SUMIF(A$2:A$9,F2,B$2:B$9)) and then copy this down to G15. Then in G16 you can have this formula: =SUM(G2:G15) to give you the total (plus a break-down of each account above). Hope this helps. Pete On Dec 31, 2:35*pm, Kevin W wrote: * This one seems easy but I can't figure it out. * * * * A * * *B 1 * Acct * * Amt 2 * 100 * * *54 3 * 101 * * *76 4 * 102 * * *84 5 * 103 * * *67 6 * 104 * * *99 7 * 105 * * *42 8 * 106 * * * 0 9 * 107 * * * 81 How can I take the total sum of accounts (for example) 102,104 & 107. *So I am looking for an answer of 84 + 99 + 81 = 264. I will normally use around 15 - 20 accounts. *Do I need a separate SUMIF formula for each account, or can I set up one and input a list of accounts for the criteria?? Thanks in advance |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF using criteria from a list
Hi Kevin,
if you are in excel 2007, select the range columns A and B, then format it as a table go to an empty row in column B and add the total. Filters will show up, select the accounts you need and the totals will change automatically "Kevin W" wrote: This one seems easy but I can't figure it out. A B 1 Acct Amt 2 100 54 3 101 76 4 102 84 5 103 67 6 104 99 7 105 42 8 106 0 9 107 81 How can I take the total sum of accounts (for example) 102,104 & 107. So I am looking for an answer of 84 + 99 + 81 = 264. I will normally use around 15 - 20 accounts. Do I need a separate SUMIF formula for each account, or can I set up one and input a list of accounts for the criteria?? Thanks in advance |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF using criteria from a list
Thanks. This works but I would really like to get a formula in one cell, if
possible. Is there any other way? I am using excel 2003 "Pete_UK" wrote: One way of doing it would be to list the accounts you want in, for example, F2:F15, leaving cells blank if not required. Then in G2 you can put this formula: =IF(F2="","",SUMIF(A$2:A$9,F2,B$2:B$9)) and then copy this down to G15. Then in G16 you can have this formula: =SUM(G2:G15) to give you the total (plus a break-down of each account above). Hope this helps. Pete On Dec 31, 2:35 pm, Kevin W wrote: This one seems easy but I can't figure it out. A B 1 Acct Amt 2 100 54 3 101 76 4 102 84 5 103 67 6 104 99 7 105 42 8 106 0 9 107 81 How can I take the total sum of accounts (for example) 102,104 & 107. So I am looking for an answer of 84 + 99 + 81 = 264. I will normally use around 15 - 20 accounts. Do I need a separate SUMIF formula for each account, or can I set up one and input a list of accounts for the criteria?? Thanks in advance |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF using criteria from a list
Hello Kevin
You can use SUMIF like this =SUM(SUMIF(A$2:A$9,{102,104,107},B$2:B$9)) or, if you have a list of accounts in F2:F15 as Pete suggests =SUMPRODUCT(SUMIF(A$2:A$9,F2:F15,B$2:B$9)) but be careful with that one because if any account is repeated in F2:F15 you'll double count it. To avoid that try =SUMPRODUCT(--ISNUMBER(MATCH(A$2:A$9,F2:F15,0)),B$2:B$9) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumif with four criteria??? | Excel Discussion (Misc queries) | |||
SumIf with OR criteria | Excel Worksheet Functions | |||
Sumif with criteria list | Excel Worksheet Functions | |||
nested sumif or sumif with two criteria | Excel Worksheet Functions | |||
SUMIF Criteria | Excel Discussion (Misc queries) |