Duke...

Here is a formula that I am using which is working very well for what I need:

=SUMPRODUCT(--(MONTH('Fall 2008'!D2:D1000)=8),--('Fall
2008'!E2:E1000="Yes"),--('Fall 2008'!F2:F1000="New"))

Is there a way I can add something to this formula so that it also looks at
a listing of course titles in a column and does not count any that are
duplicated? For example, if I have an "Economics" course that is being
taught by three instructors, each has a different course number, but I want
to only count the "Economics" course one time. Right now, the way the
formula is set up, it's counting every instance (section) of the "Economics"
course. I would probably want to filter this by the course name column,
right?


"Duke Carey" wrote:

A few options:

1) name each of the ranges, then use the range name in your formulas
2) have your formulas ON the Fall 2007-08 (1st term) tab, then reference the
calculated values on the reports tab
3) copy the text of the formula to the reports tab. Edit it and select the
data range address, click on the Fall 2007-08 (1st term) sheet tab and
highlight the range of dates. Excel will replace theoriginal address with an
address qualified with the sheet name. Repeat for the other two range
addresses

"Chris Hofer" wrote:

We're getting closer, Duke...

What if I want to put this formula in my "Reports" tab and then calculate
the values from a tab called "Fall 2007-08 (1st term)"? How do I specify
that "Fall" tab in the forumla you've given me?


"Duke Carey" wrote:

Using your example range, and assuming the dates are in column D, new range
in F

=SUMPRODUCT(--(MONTH(D2:D1000)=8),--(E2:E1000="yes"),--(F2:F1000="New"))




"Chris Hofer" wrote:

I think I'm understanding a litle bit of this, Duke. My Excel skills aren't
probably what they should be these days, so am I actually specifying a range
of cells (such as E2:E1000) for the "blendedrange" and "newrange" data? I'd
also have to somehow specify that for "daterange", too, right?

"Duke Carey" wrote:

try this for August, yes, and new (all ranges must be of equal size)

=SUMPRODUCT(--(MONTH(daterange)=8),--(blendedrange="yes"),--(newrange="New"))


if you need to restrict for year as well as month, use

=sumproduct(--(month(daterange)=8),--(year(daterange)=2007),--(blendedrange="yes"),--(newrange="New"))