Summing multiple account #s in one cell, by referencing

[Matt]

Question involves summing a variety of accounts in one cell, on a different
worksheet (In this example, say Sheet 3). In Sheet1, I have a list of
account #s and corresponding values in two columns (my raw data). In sheet
2, I would like to build a special database for the future of which accounts
I would like to pick from Sheet 1. Then, in Sheet 3, I would like to
reference Sheets 1 and 2, by referencing those account numbers in Sheet 2 I
want, and seeing the total values from Sheet 1 on Sheet 3, in just one cell.
I tried sumIFs functions, but it doesn't seem like I can have multiple
"criteria" values from Sheet 2 (I can just reference one cell, but I want to
total multiple accounts). Is this possible to do in one simple formula that
can just reference the sum range (Sheet 1 column C, the criteria range (Sheet
1 column A) and the "multiple critera" (Sheet 2, column A)???


Say I want to sum in one cell, on Sheet 3, the total value of accounts 10,
12, 13 (by specifying only those accounts in the formula from Sheet 2), which
would give the value of 85 (15 + 20 + 50) in one cell on Sheet 3:

Description Account#s / Values / Only account #s I want to sum

Worksheet 1 / 1 / 2

Row / Column A / C / A
1 10 15 10
2 11 30 12
3 12 20 13
4 13 50
5 14 40
6 15 30

 

[Duke Carey]

the DSUM() function will do what you want.

Make sure you have column headers for the account #s and the data, and use
the same column header on sheet 2 for your list of accounts

 

[Matt]

OK, many thanks! Any way you could possible right the equation for me using
the example I wrote (If I was clear enough?). I can't wrap my head around
how to use this function yet...

Thanks

 

[Duke Carey]

=DSUM(Sheet1!B1:C7,Sheet1!C1,Sheet2!A1:A4)

where
- Sheet1!B1:C7 contains your data - including the column headers
- Sheet1!C1 is the column header for the data to be summed
- Sheet2!A1:A4 contains the same column header as Sheet1!B1 plus the list
of accounts to sum

 

[Matt]

Almost there! I put in this function (so you can see the account#s or Sheets
2 and 3, values in Sheet two column I).

=DSUM(Sheet2!A2:I50,Sheet2!I2,Sheet3!A2:A50)

But the function result, no matter what accounts I write in my range in
Sheet 3, is just the total sum of the range of A2:A50. It doesn't change no
matter what account numbers I write there?