Results 1 to 5 of 5

20010731, 17:21 #1
 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.

20010731, 19:03 #2
 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

20010731, 20:26 #3
 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?

20010731, 20:33 #4
 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

20010801, 18:48 #5
 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!!