need help in formula

[kyoko]

i really need help for this one:

A B C
oct 27 -50 cover
oct 27 -50 not cover
oct 27 50 cover
oct 28 150 cover
oct 28 -100 cover
oct 29 -50 cover

in this case, i need the number of "cover" from oct 27 to oct 29 that falls
under -50 only. is there any way to make it easier for me to get the number
of it? please help me..i will appreciate it a lot..thanks in advance

 

[kyoko]

sorry i posted the same problem with the first one 'coz i thought my first
post didn't appear here...its just the same...hope someone help me here...

 

[T. Valko]

Try this...

Use cells to hold the criteria.

E2 = lower date boundary = 10/27/2009
F2 = upper date boundary = 10/29/2009
G2 = -50%
H2 = cover

Then:

=SUMPRODUCT(--(A2:A7>=E2),--(A2:A7<=F2),--(B2:B7=G2),--(C2:C7=H2))

 

[kyoko]

thanks for that but is there any other formula rather than that because it's
not so applicable, im still having a problem with it..

 

[Joe User]

i need the number of "cover" from oct 27 to oct 29 that falls
under -50 only.

It is not clear whether column A has actual dates formatted as "mmm dd", or
if it contains text of the form "oct 27".

Assuming column A contains actual dates, perhaps the following will work for
you, assuming you example data are in rows 2-7:

=SUMPRODUCT((D1<=A2:A7)*(A2:A7<=D2)*(B2:B7<=-50)*(C2:C7="cover"))

where:
D1, starting date: Oct 27
D2, ending date: Oct 29

D1 and D2 should be entered the same way that you entered dates in column A.
Alternatively, instead of D1 and D2, you could use the DATE function,
namely: DATE(2009,10,27) and DATE(2009,10,29).

Note: That formula might seem to work even if column A contains text of the
form "oct 27". But that is only by coincidence. For example, it would not
work if the starting date was "oct 17" as text. "oct 2" would incorrectly
seem to be between "oct 17" and "oct 29".

If you do have dates in text form, I suggest that you change them to actual
dates. Otherwise, it is very difficult to implement a condition like
"between this date and that date".

Also note: You wrote "falls __under__ -50". If that is truly what you
meant, change "<=-50" above to "<-50". But then your example has no lines
that qualify. So I assume you meant "not over -50". Alternatively, if
"falls under -50" meant "is equal to -50", change "<=-50" to "=-50".


----- original message -----

 

[T. Valko]

it's not so applicable

Why do you say that? You want to count for multiple conditions, don't you?

im still having a problem with it..

Care to explain what that problem might be?

Maybe I've misunderstood what it is you want to do. My interpretation of
your post is you want to count based on conditions: dates that fall within a
range of dates, % = -50% and text = cover. The formula I suggested does
exactly that. Based on your posted sample data my formula returns 2.

 

[Joe User]

It is not clear whether column A has actual dates formatted as "mmm dd",
or if it contains text of the form "oct 27".

Assuming column A contains actual dates, perhaps the following will work
for you, assuming you example data are in rows 2-7:

=SUMPRODUCT((D1<=A2:A7)*(A2:A7<=D2)*(B2:B7<=-50)*(C2:C7="cover"))

where:
D1, starting date: Oct 27
D2, ending date: Oct 29

D1 and D2 should be entered the same way that you entered dates in column
A. Alternatively, instead of D1 and D2, you could use the DATE function,
namely: DATE(2009,10,27) and DATE(2009,10,29).

Note: That formula might seem to work even if column A contains text of
the form "oct 27". But that is only by coincidence. For example, it
would not work if the starting date was "oct 17" as text. "oct 2" would
incorrectly seem to be between "oct 17" and "oct 29".

If you do have dates in text form, I suggest that you change them to
actual dates. Otherwise, it is very difficult to implement a condition
like "between this date and that date".

Also note: You wrote "falls __under__ -50". If that is truly what you
meant, change "<=-50" above to "<-50". But then your example has no lines
that qualify. So I assume you meant "not over -50". Alternatively, if
"falls under -50" meant "is equal to -50", change "<=-50" to "=-50".


----- original message -----

 

[Joe User]

PS....

=SUMPRODUCT((D1<=A2:A7)*(A2:A7<=D2)*(B2:B7<=-50)*(C2:C7="cover"))

I missed the fact that you wrote percentages in column B in your other
thread, albeit not in this thread. If that's what you mean, then:

=SUMPRODUCT((D1<=A2:A7)*(A2:A7<=D2)*(B2:B7<=-50%)*(C2:C7="cover"))


----- original message -----

 

[Joe User]

Errata...

Also note: You wrote "falls __under__ -50". If that is truly what you
meant, change "<=-50" above to "<-50". But then your example
has no lines that qualify.

My bad: I overlooked the line with -100. Nonetheless, I still wonder if
you truly meant "not over -50", i.e. less than or equal to -50(%).

Finally, considering that you wrote percentages in column B in one thread
and non-percentages in this thread, I wonder if column B contains true
percentages, or if it contains integers (or real numbers) that you read as
percentages.


----- original message -----

 

[kyoko]

its ok now...sorry, i key in wrong data that's why it didn't apply
clearly..thanks a lot T. Valko...i appreciate your help!!thanks so much..^_^

 

[T. Valko]

OK, good deal. Thanks for the feedback!