I think I see where you are going with this, but not sure that it's exactly
what I need. Basically, in my "Reports" tab using the formula I initially
posted, I can get a count of how many courses (this includes all courses that
have duplicate names, too) fit a certain criteria (giving the criteria I
explained in Columns E and F previously). My supervisor has a spreadsheet of
her own that she wants us to update from time to time, but she only wants
numbers for non-duplicated courses (so again, if that "College Math" is
listed 3 times for us because of unique course numbers, we have to count this
as 1 course). So, for us, it's a matter of gathering a count of
non-duplicated course names in our spreadsheet with specific criteria and
entering that number (count) in another spreadsheet.
Does this help?
:
Hi Chris,
I'm not sure I understand what you want to do. If you want a unique
list of course names then you could copy column C to a new sheet,
highlight the names and the header and then Data | Filter | Advanced
Filter and in the pop-up you can select Unique records only and Copy
them to a different location (eg $C$1). You could then get rid of
columns A and B in this new sheet, and maybe sort column A.
If you want a count of how many entries you have for each course name,
then in B2 you could enter:
=COUNTIF('Fall 2008'!C$2:C$1000,A2)
and copy this down column B for as many entries as you have in column
A.
Is this what you want?
Hope this helps.
Pete
On Apr 30, 2:30 pm, Chris Hofer <
[email protected]>
wrote:
Hello again, Pete...
Thanks for your response. I think I am partially understanding this. Since
column C is the column that contains all course names (there are several
different course names...some duplicated...some not), is it possible to look
at all course names and give me a count of courses with no duplication? Am I
understanding your latest reply correctly that the additional part of the
formula is only searching for the "Introduction to College Math" course?
What if I had this example in Columns B & C:
B C
804-106-011 INTRODUCTION TO COLLEGE MATH
804-106-014 INTRODUCTION TO COLLEGE MATH
804-106-016 INTRODUCTION TO COLLEGE MATH
152-137-001 JAVA PROGRAMMING
152-137-002 JAVA PROGRAMMING
In the formula I'm using, is it possible to have some kind of wildcard or
something that would look at the course titles in Column C and just count the
"Intro to College Math" course one time and the "Java Programming" course one
time as well [even though there are unique course section numbers (Column
B)]? I currently have over 100 rows of courses (again, some course titles
duplilcated...some not).
Thanks.
Chris
:
Hi Chris,
You can add another condition like so:
=SUMPRODUCT(--(MONTH('Fall 2008'!D2
1000)=8),--('Fall 2008'!
E2:E1000="Yes"),--('Fall 2008'!F2:F1000="New"),--('Fall 2008'!
C2:C1000="Introduction to College Math"))
It would be better if you put "Introduction to College Math" (without
the quotes) in a cell somewhere (say, A1 on the same sheet as the
formula) and then reference it within the formula. I also prefer this
form:
=SUMPRODUCT((MONTH('Fall 2008'!D2
1000)=8)*('Fall 2008'!
E2:E1000="Yes")*('Fall 2008'!F2:F1000="New")*('Fall 2008'!
C2:C1000=A1))
where the * can be read as the AND operator. You can easily change the
entry in A1 to get a result for another course - NOTE that it has to
match the course title exactly, so you might want to apply data
validation on A1 to look at a list of course names elsewhere. This
could also be done for column C of your 'Fall 2008' sheet.
Hope this helps.
Pete
On Apr 27, 6:06 pm, Chris Hofer <
[email protected]>
wrote:
Sure...not a problem...
To answer your first question, I am not actually checking the course number
at all in any formulas I have set up.
In column E, I have a drop-down list set up where the heading is called
"Blended". If the couse fits this criteria, we select "Yes" from the
drop-down. If not, we select "No".
In column F, I have a drop-down list set up where the heading is called "New
OR Revised". If the course is brand new or revised, we select the
appropriate choice ("New" or "Revised") from the drop-down list.
The course number resides in column B. Generally, this is a nine-digit
number which varies from course to course. In the "College Math" course
example, the number is 804-106-### where ### is a three digit course section
number. I have this column set up where I can enter in a string of 9 digits
(ex: 123456789) and it will automatically add the dashes for me (ex:
123-456-789) upon hitting Enter.
Column C contains the course title. Again, in the example, the course title
would be "Introduction to College Math", but I only want to count that title
one time in my formula even though we have three unique course section
numbers for it (example: 804-106-001, -002, -003)
Chris
:
Where are you checking for the course number now? This formula is
looking for month=8 in column D, column E="Yes" and column F = "New".
What do the columns E and F represent? Presumably you have a field
where the course title is recorded?
Please give further details of the layout of your data.
Pete
On Apr 27, 3:26 pm, Chris Hofer <
[email protected]>
wrote:
Here is a formula that I am using which is working very well for what I need:
=SUMPRODUCT(--(MONTH('Fall 2008'!D2
1000)=8),--('Fall
2008'!E2:E1000="Yes"),--('Fall 2008'!F2:F1000="New"))
This formula is in a worksheet tab called "Reports", and it's pulling data
from my "Fall 2008" tab.
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 "Introduction to College Math" course
that is being taught by three instructors, each has a different course
number, but I want
to only count the "Introduction to College Math" course one time. Right
now, the way the formula is set up, it's counting every instance (section) of
the "Introduction to College Math" course. I would probably want to filter
this by the course name column.- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -- Hide quoted text -
