Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Jan 2002
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    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
    Attached Files Attached Files
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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>

    Legare Coleman

Posting Permissions

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