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

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

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

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

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)