Count of Data Based on Multiple Columns

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have two columns in my spreasheet that I'd like to get a count of numbers.

*These columns are in a tab called "Fall 2007-08 (1st term)"

The first column (E) has a heading of "BLENDED". If the course is blended,
then we select "Yes" from the dropdown list I've created. If it's not
blended (a.k.a. online), then we select "No".

The second column (F) has a heading of "NEW or REVISED". In in the cells
for this column, we can select from another list the values of "New" or
"Revised".

I also have a tab called "Reports" which contains a lot of calculated data
based on this first tab. I would like to create 4 formulas in the "Reports"
tab that would show the total number of courses that are:

- Blended (yes) and New
- Blended (yes) and Revised
- Not Blended (no) and New
- Not Blended (no) and Revised

Further, I also have a start date column which contains the official start
date of a course. If I could somehow separate out these 4 formulas even
further to tell me which are for August, September, October, etc., that would
be helpful, too.
 
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"))
 
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?
 
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"))
 
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?
 
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
 
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?
 
Back
Top