If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
need help in formula
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 |
#2
|
|||
|
|||
need help in formula
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... "kyoko" wrote: 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 |
#3
|
|||
|
|||
need help in formula
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)) -- Biff Microsoft Excel MVP "kyoko" wrote in message ... 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 |
#4
|
|||
|
|||
need help in formula
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.. "T. Valko" wrote: 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)) -- Biff Microsoft Excel MVP "kyoko" wrote in message ... 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 . |
#5
|
|||
|
|||
need help in formula
"kyoko" wrote:
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")) whe 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 ----- "kyoko" wrote in message ... 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 |
#6
|
|||
|
|||
need help in formula
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. -- Biff Microsoft Excel MVP "kyoko" wrote in message ... 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.. "T. Valko" wrote: 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)) -- Biff Microsoft Excel MVP "kyoko" wrote in message ... 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 . |
#7
|
|||
|
|||
need help in formula
"Joe User" joeu2004 wrote in message ... "kyoko" wrote: 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")) whe 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 ----- "kyoko" wrote in message ... 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 |
#8
|
|||
|
|||
need help in formula
PS....
I wrote: =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" joeu2004 wrote in message ... "kyoko" wrote: 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")) whe 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 ----- "kyoko" wrote in message ... 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 |
#9
|
|||
|
|||
need help in formula
Errata...
I wrote: 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 ----- "Joe User" joeu2004 wrote in message ... "kyoko" wrote: 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")) whe 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 ----- "kyoko" wrote in message ... 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 |
#10
|
|||
|
|||
need help in formula
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" wrote: 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. -- Biff Microsoft Excel MVP "kyoko" wrote in message ... 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.. "T. Valko" wrote: 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)) -- Biff Microsoft Excel MVP "kyoko" wrote in message ... 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 . . |
|
Thread Tools | |
Display Modes | |
|
|