# Thread: Conditional Sum (Excel 2000)

1. ## Conditional Sum (Excel 2000)

I am trying to do a conditional sum for a total amount depending on the location and account number of an entry. There are a handful of accounts that we use all the time, but many more that we don't. I'd like to do a conditional sum, but be able to enter the value that the conditional sum looks for. I basically need to know if/how I can have a cell reference within the conditional sum formula.

2. ## Re: Conditional Sum (Excel 2000)

TR, I think that the nicest way is to use the SUBTOTAL function and autofilter your list. SUBTOTAL only operates on visible cells and the beancounters can check your work. If the data is in A2:C6 with the amount in column C, then =SUBTOTAL(9,C2:C6) gives the sum.

You can also use an array formula to produce the desired results. If the data is in A9:C13 with account in A, location in B, amount in C and constraints in row 15, then <pre>{=SUM(IF((A9:A13=A15)*(B9:B13=B15),C9:C13,0)) }</pre>

Note that the braces {} are added by Excel when you press <Ctrl><Shift><Enter> to enter the formuls. I have attached a workbook that shows both ways. HTH --Sam

3. ## Re: Conditional Sum (Excel 2000)

You can use the formula below to sum the values in B1:B10 where the Account Number in A1:A10 is equal to the Account Number in C1:

<pre>=SUMIF(A1:A10,\$C\$1,B1:B10)
</pre>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•