Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Warsaw, Indiana, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Help With A Formula (Excel 97, SR 2)

    I need help figuring out the proper functions and syntax for a formula.

    I want a formula that says: Sum all of the A's in the Categories column (Column A) that have been invoiced between 1/01/01(first day of the month) and 1/31/01(last day of the month) in Column B and total the amounts for these items.

    Here's how it's set up:

    Column A = Categories (A, C or O)
    Column B = Invoice Date (displayed like 1/1/01)
    Column C = Customer Name
    Column D = Amount
    Column E is blank
    Column F = Months of the Year (Jan. thru Dec.)

    Column G = Total Amount for all the A's from 1/1/01 to 1/31/01
    Column H = Total for all the C's
    Column I = Same for all the O's

    In Column G, I want to put that formula in the cell next to Jan. and display the total amount for all the Category A's. Then copy the formula in column H and I to dispplay sums for the C's and O's.

    Thanks for any help you can provide.

    Thanks for any help you can give.

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

    Re: Help With A Formula (Excel 97, SR 2)

    This array formula in cell G1 should give you the sum of the amounts for Category A in Jan. To make this an array formula hold down the Ctrl and Shift keys when you press enter:

    <pre>=SUM((A1:A9="A")*(B1:B9>=DATEVALUE("1/1/01"))*(B1:B9<=DATEVALUE("1/31/01"))*D19)
    </pre>


    To get the sum of Categories C and O, use a similar formula with the "A" changed to "C" and "O".
    Legare Coleman

  3. #3
    Lounger
    Join Date
    Jan 2001
    Location
    Warsaw, Indiana, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help With A Formula (Excel 97, SR 2)

    Thanks Legare:

    Thanks!! The formula worked. However, I have one more question. I typed the formula in G2 to get the total of all the A's for Jan. But I want all of the A's for Feb., Mar, and so on.

    Also I had to type the formula in H2 and I2 to get the appropriate result. Do I always need to type the formula in each cell and then do Ctrl Shift Enter to get the totals for the rest of totals?

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

    Re: Help With A Formula (Excel 97, SR 2)

    You could put the category, start date, and end date into cells and use those cells in the formula. That way, you can change which you get by just typing new values into those cells.

    If you want to create 36 cells with all of the totals, then you will need to copy and paste the formula into all of the cells and then change the category and dates in the formula and use Ctrl/Shift/Enter.
    Legare Coleman

  5. #5
    Lounger
    Join Date
    Jan 2001
    Location
    Warsaw, Indiana, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help With A Formula (Excel 97, SR 2) THANKS

    Thanks again for your help. I think you answered my question when you said to press Ctrl+Shift+Enter. I originally thought I could do an array, copy it and have it work okay in every cell. I see now that I have to press Ctrl+Shift+Enter after I make the appropriate changes in each cell.

    Thanks again!!

Posting Permissions

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