multiple conditons

[Bob N.]

Hi there all,
I am trying to create a time management plan that can
calculate how many hours I am sleeping, procrastinating,
and work I should be doing.

my table looks somthing like this

____ _____________________________________________________
|--| |----a----|----b----|----c----|----d-----|----N-----|
|__| |___________________________________________________|
|--| |---Time--|Sun------|Mon------|Tues------|types of--|
---| |---------|---------|---------|----------|classes---|
|__| |_________|_________|_________|__________|__________|
|2-| |6:00 am--|sleep----|sleep----|sleep-----|portfolio-|
---| |---------|---------|---------|----------|class-----|
|__| |_________|_________|_________|__________|__________|
|3-| |7:00 am--|sleep----|breakfast|breakfast-|multimedia|
---| |---------|---------|---------|----------|class-----|
|__| |_________|_________|_________|__________|__________|
|4-| |8:00 am--|breakfast|shower---|shower----|math------|
---| |---------|---------|---------|----------|class-----|
|__| |_________|_________|_________|__________|__________|
|5-| |9:00 am--|shower---|portfolio|multimedia|----------|
---| |---------|---------|class----|class-----|----------|
|__| |_________|_________|_________|__________|__________|
|6-| |10:00 am-|call mom-|portfolio|multimedia|----------|
---| |---------|---------|class----|class-----|----------|
|__| |_________|_________|_________|__________|__________|
|7-| |11:00 am-|visit mom|Math-----|portfolio-|----------|
|--| |---------|---------|class----|homework--|----------|
|__| |_________|_________|_________|__________|__________|


and so on...
if you can see my problem is "protfolio class" is in the
same catagory as "multimedia class", which they are both
in common as they are classes. To make it more
complicated, I don't won't to change "portfolio class"
or "multimedia class" to just saying the word "class"
because I am tring to be as specific as I can, but I want
to catorgorize them as the same. So what I did was I
created an extra colum, which is colum "N" that list all
the possible words that could mean the samething. The
problem with this is I did a formula like so...

=countif(c2:c6, n2:n4) in cell "M1", and the result came
back as 0, when it should of return 2 because c5 and c6
both match in cell n2. So I did this in cell "m1"...

=SUM(COUNTIF(c2:c6, n2), COUNTIF(c2:c6, n3), COUNTIF
(c2:c6, n4))

.... but the problem with this is what if one week my
range is from n2:n16 and another week the range is n2:n100

is there a faster way?

 

[Myrna Larson]

I didn't follow everything exactly, but be aware that you can use wildcards
with a countif statement, like this

=COUNTIF(N1:N30,"*class*")

Maybe that will take care of it for you.

 

[Guest]

That could work, but since you said "I didn't follow
everything exactly...", let me clarify this and make sure
if this is the only solution

Imagine your building a time management plan and you are
eventually going to build a bar chart that are
catogorised as one bar would stand for the percentage of
sleeping time, another bar would stand for the percentage
of procrastination, and another bar would stand for
things that you can't improve time on such as you must
take a shower ever day and you must eat at least 3 meals
a day and so on. So the sleeping bar is very easy to
calculate because it is only one word, but shower,
eating, or going to school ect are all things that you
can't improve time on but countif doesn't know that these
types of words are in the same catorgory without you
specifing what to look for. thats what I am looking for.
Are there any other solutions to this?

 

[Myrna Larson]

You can combine categories by adding the results of several COUNTIF
statements"


=COUNTIF(A1:A10,"breakfast")+COUNTIF(A1:A10,"lunch")+COUNTIF(A1:A10,"dinner")