Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Still having trouble
I want to return information per month.
Can I do something like this; =IF(MONTH=APRIL,COUNTIF(B:B,"CUSTOMER ERROR"),) Obviously MONTH=APRIL is not the proper language, but I am having trouble using other date functions. I want to return a number of how many times a customer caused the default in an order per month. This was reccomended earlier: =SUMPRODUCT(--(S2:S999="CUSTOMER ERROR"),--(MONTH(B2:B999)=5)) Still didn't work. Maybe a dumb question, but what does the "--" mean? Any help is appreciated. Thanks so much Scott |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Still having trouble
should work for MAY if col S has the "customer error" and col B has properly
formatted dates -- Don Guillett SalesAid Software "scott" wrote in message ... I want to return information per month. Can I do something like this; =IF(MONTH=APRIL,COUNTIF(B:B,"CUSTOMER ERROR"),) Obviously MONTH=APRIL is not the proper language, but I am having trouble using other date functions. I want to return a number of how many times a customer caused the default in an order per month. This was reccomended earlier: =SUMPRODUCT(--(S2:S999="CUSTOMER ERROR"),--(MONTH(B2:B999)=5)) Still didn't work. Maybe a dumb question, but what does the "--" mean? Any help is appreciated. Thanks so much Scott |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Still having trouble
the "--" changes the logical true false of the equation in the parenthesis to
a numeric 1 or 0 the sumproduct will normally work in a situation like this. are you sure your date is a date and not text? for one of the date fields that shoud be April (b2) in an empty cell try =month(B2)=4 (your example says "5" was this for a May question?) if the answer is not "true" then it is not a date field and a different matching equation needs to be used. do something similar to the "customer error" column. if there is a chance of leading or trailing spaces you might try trim(S2:S999) "scott" wrote: I want to return information per month. Can I do something like this; =IF(MONTH=APRIL,COUNTIF(B:B,"CUSTOMER ERROR"),) Obviously MONTH=APRIL is not the proper language, but I am having trouble using other date functions. I want to return a number of how many times a customer caused the default in an order per month. This was reccomended earlier: =SUMPRODUCT(--(S2:S999="CUSTOMER ERROR"),--(MONTH(B2:B999)=5)) Still didn't work. Maybe a dumb question, but what does the "--" mean? Any help is appreciated. Thanks so much Scott |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Still having trouble
thanks for your responses!
I tried =SUMPRODUCT(--(S2:S99="CUSTOMER ERROR"),--(MONTH(B2:B99)=5)) and it returned #VALUE My dates are correct; entered as =date(2006,5,17) any ideas where this error is coming from? "bj" wrote: the "--" changes the logical true false of the equation in the parenthesis to a numeric 1 or 0 the sumproduct will normally work in a situation like this. are you sure your date is a date and not text? for one of the date fields that shoud be April (b2) in an empty cell try =month(B2)=4 (your example says "5" was this for a May question?) if the answer is not "true" then it is not a date field and a different matching equation needs to be used. do something similar to the "customer error" column. if there is a chance of leading or trailing spaces you might try trim(S2:S999) "scott" wrote: I want to return information per month. Can I do something like this; =IF(MONTH=APRIL,COUNTIF(B:B,"CUSTOMER ERROR"),) Obviously MONTH=APRIL is not the proper language, but I am having trouble using other date functions. I want to return a number of how many times a customer caused the default in an order per month. This was reccomended earlier: =SUMPRODUCT(--(S2:S999="CUSTOMER ERROR"),--(MONTH(B2:B999)=5)) Still didn't work. Maybe a dumb question, but what does the "--" mean? Any help is appreciated. Thanks so much Scott |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trouble Forecasting | Excel Discussion (Misc queries) | |||
Trouble with formulas | Excel Discussion (Misc queries) | |||
UGH! Trouble downloading, and opening anything | Excel Discussion (Misc queries) | |||
still having trouble with this.... | Excel Worksheet Functions | |||
I have had trouble with textbox text to worksheet | Excel Discussion (Misc queries) |