Excel Formula Help

[Mark]

I am looking for some help in writing a formula in Excel.
I have dates in column "A" and dollar values in
column "F". I am looking for a formula that will look at
the dates from 1/1/2004 to 3/31/2004 and add up the values
in column "F" accordingly. Basically this will be setup on
a quarterly basis. So it has to look at just the values
between that 3 month period. I have tried IF, SUMIF, AND,
OR, functions, but could not find the right combination.
Any help would be greatly appreciated.

Thank you.

 

[Norman Harker]

Hi Mark!

Formula that sums between those two dates (inclusive) is:
=SUMPRODUCT(($A$1:$A$100>=C1)*($A$1:$A$100<=D1)*$F$1:$F$100)

Just put your starting date in C1 and ending date in D1 [or select
other cells]

Those absolute references allow you to copy down and get totals for
other periods.

But you might consider this a good exercise for you to try pivot
tables.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[S Austin]

I solved a similar issue by using an Excel 4.0 macro. I
checked each cell in the date column to see if it fell
within my range, then, if so, added the value in amount
column. My column was in date order so once I found a
date above my max range I knew I was done with the total.
Email me direct if you want to discuss further. And let
me know if a simpler method appears. Steve.

 

[Hilken]

I opt for formulas when possible so I do NOT have to worry
about any changes directly or in links.

First alt.
Add a column for each quarter
you can put the start (SDay) and end (EDay) dates for
each quarter in two cells the 2nd and 3rd row of
each column or elsewhere for easy annual changing
or directly in the formula.
in the first data row of each quarter's column
=IF(AND(Ax >= SDay, Ax <= EDay), Fx,"")
double quotes "" is the null or blank character =
No zeros
Cells for each column's (quarter's) total can put as
desired
If at the beginning or end of the data, you can
hides unwanted rows to print a quarterly report
AT the head allows easy view of quarter totals

Second alt.
Select all applicable rows
Sort by column A "Date"
add a total cell for each quarter range as desired.
=sum(Fx:Fy) where x, y start end rows for each
quarter
copy formula cells down as needed
If using cell references for the dates lock the
reference by pressing F4 key or $col$row
before copying
Note: after testing you formulas, delete all data,
name the sheet "template" or such and then copy it
in the same book (or another) and name it for the
year + other reference if needed.