Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF HELP???
I am trying to count how many times a customer has a discrepancy using a
COUNTIF formula. I have set up: =COUNTIF(S:S,"CUSTOMER") - where S:S is a column of drop down lists where "CUSTOMER" can be selected. However, I need to have counts per month. I tried to specify the data per month like: =IF(MONTH(B:B)=5,COUNTIF(S:S,CUSTOMER),) - where B:B is a column of dates. Is this even possible? Do I need to enter my dates as =DATE(2006,5,16)? Thanks for your time. Scott |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF HELP???
for month of Dec you can use the following formula:
=SUMPRODUCT(--(S:S="Customer"),--(B:B="Dec")) and so on.... "scott" wrote: I am trying to count how many times a customer has a discrepancy using a COUNTIF formula. I have set up: =COUNTIF(S:S,"CUSTOMER") - where S:S is a column of drop down lists where "CUSTOMER" can be selected. However, I need to have counts per month. I tried to specify the data per month like: =IF(MONTH(B:B)=5,COUNTIF(S:S,CUSTOMER),) - where B:B is a column of dates. Is this even possible? Do I need to enter my dates as =DATE(2006,5,16)? Thanks for your time. Scott |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF HELP???
For more than one condition SUMPRODUCT can be used =SUMPRODUCT(--(MONTH(B1:B100)=5),--(S1:S100="CUSTOMER")) note you can't use whole columns as with COUNTIF -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=542722 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF HELP???
=SUMPRODUCT(--(S2:S200="CUSTOMER"),--(MONTH(B2:B200)=5))
Note that SUMPRODUCT doesn't work with complete columns, you have to specify a range. -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "scott" wrote in message ... I am trying to count how many times a customer has a discrepancy using a COUNTIF formula. I have set up: =COUNTIF(S:S,"CUSTOMER") - where S:S is a column of drop down lists where "CUSTOMER" can be selected. However, I need to have counts per month. I tried to specify the data per month like: =IF(MONTH(B:B)=5,COUNTIF(S:S,CUSTOMER),) - where B:B is a column of dates. Is this even possible? Do I need to enter my dates as =DATE(2006,5,16)? Thanks for your time. Scott |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF HELP???
sorry just make sure you have a correct range in there,
=SUMPRODUCT(--(S1:S1000="Customer"),--(B1:B1000="Dec")) "Nikki" wrote: for month of Dec you can use the following formula: =SUMPRODUCT(--(S:S="Customer"),--(B:B="Dec")) and so on.... "scott" wrote: I am trying to count how many times a customer has a discrepancy using a COUNTIF formula. I have set up: =COUNTIF(S:S,"CUSTOMER") - where S:S is a column of drop down lists where "CUSTOMER" can be selected. However, I need to have counts per month. I tried to specify the data per month like: =IF(MONTH(B:B)=5,COUNTIF(S:S,CUSTOMER),) - where B:B is a column of dates. Is this even possible? Do I need to enter my dates as =DATE(2006,5,16)? Thanks for your time. Scott |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Countif Formula /Sort Bug??? | Excel Discussion (Misc queries) | |||
COUNTIF with Logic? | Excel Worksheet Functions | |||
COUNTIF or not to COUNTIF on a range in another sheet | Excel Worksheet Functions | |||
Combining IF and COUNTIF based on two columns | Excel Discussion (Misc queries) | |||
Countif - Countif | Excel Worksheet Functions |