Including a cell in a greater than equation?
Posted by Greg on August 20, 2001 5:16 AM
This may sound simple but its baffling me. I need a way to count up all of the occurances of things between two dates. The two dates are four days ago as the end point and a week and four days ago as the starting point. For example, if today the 20th then I want to count all of the occurances from the 9th till the 16th.
I am trying to do this using a countif and a date column. I try do this by using todays date (as a number something like 37120) subtract 4 then use a <A30 statement to say all dates that are below the contents of a30 but it thinks I'm writing "<a30" as a word not an equation.
I would really appreciate some help with this. I've been stuck since Friday.
-Greg
Posted by Aladin Akyurek on August 20, 2001 5:52 AM
Greg,
In A1 enter: =TODAY()-4
In A2 enter: =A1-7
I'll suppose that you have the dates of interest in B from B2 on and the things to count in C from C2 on.
Use
=SUMPRODUCT((B2:B100>=$A$2)*(B2:B100<=$A$1)*(C2:C100))
Note that I used an inclusive between, so adjust it if necessary.
Aladin
=========
