Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countif wildcards & 2 criteria
Hi,
I've been trying for 2 days to make this formula work. I have a spreadsheet that shows the employees that have been hired and the ones that have left the company. I need to count how many have arrived for each month. Column E has the date and Column F has the status (True for arrivals and False for departures). I need to have a formula that would combine: =COUNTIF(E3:E276, "TRUE") and =COUNTIF(E3:E276, "2007-01-*") (I'm not sure about the use of the wildcard in a formula) Any help would be greatly appreciated Thanks Jeanne |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countif wildcards & 2 criteria
Try putting an equal sign in the formula just before TRUE;
=COUNTIF(E3:E276, "=TRUE") Les "Jeannie" wrote: Hi, I've been trying for 2 days to make this formula work. I have a spreadsheet that shows the employees that have been hired and the ones that have left the company. I need to count how many have arrived for each month. Column E has the date and Column F has the status (True for arrivals and False for departures). I need to have a formula that would combine: =COUNTIF(E3:E276, "TRUE") and =COUNTIF(E3:E276, "2007-01-*") (I'm not sure about the use of the wildcard in a formula) Any help would be greatly appreciated Thanks Jeanne |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countif wildcards & 2 criteria
Assuming the dates are real dates
=SUMPRODUCT(--(MONTH(E3:E276)=1),--(F3:F276="TRUE")) If the TRUE is a Boolean true (and since it is in caps, it look as if it is) then we can shorted formula to =SUMPRODUCT(--(MONTH(E3:E276)=1),--(F3:F276)) For more details on SUMPRODUCT Bob Phillips http://www.xldynamic.com/source/xld.SUMPRODUCT.html J.E McGimpsey http://mcgimpsey.com/excel/formulae/doubleneg.html best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Jeannie" wrote in message ... Hi, I've been trying for 2 days to make this formula work. I have a spreadsheet that shows the employees that have been hired and the ones that have left the company. I need to count how many have arrived for each month. Column E has the date and Column F has the status (True for arrivals and False for departures). I need to have a formula that would combine: =COUNTIF(E3:E276, "TRUE") and =COUNTIF(E3:E276, "2007-01-*") (I'm not sure about the use of the wildcard in a formula) Any help would be greatly appreciated Thanks Jeanne |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countif wildcards & 2 criteria
Try
=SUMPRODUCT((A1:A100=DATE(2008,1,9))*(B1:B100=TRUE )) Adjust the date formula as needed. HTH, JP On Jan 9, 12:37*pm, Jeannie wrote: Hi, I've been trying for 2 days to make this formula work. *I have a spreadsheet that shows the employees that have been hired and the ones that have left the company. *I need to count how many have arrived for each month. Column E has the date and Column F has the status (True for arrivals and False for departures). I need to have a formula that would combine: =COUNTIF(E3:E276, "TRUE") and =COUNTIF(E3:E276, "2007-01-*") (I'm not sure about the use of the wildcard in a formula) Any help would be greatly appreciated Thanks Jeanne |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countif wildcards & 2 criteria
Columns E and F????
=sumproduct(--(text(e3:e276,"yyyymm")="200701"),--(f3:f276=True)) Adjust the ranges to match--but you can't use whole columns (except in xl2007). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html Jeannie wrote: Hi, I've been trying for 2 days to make this formula work. I have a spreadsheet that shows the employees that have been hired and the ones that have left the company. I need to count how many have arrived for each month. Column E has the date and Column F has the status (True for arrivals and False for departures). I need to have a formula that would combine: =COUNTIF(E3:E276, "TRUE") and =COUNTIF(E3:E276, "2007-01-*") (I'm not sure about the use of the wildcard in a formula) Any help would be greatly appreciated Thanks Jeanne -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countif wildcards & 2 criteria
I tried all of these commands and yes, finally, the last one was the right
one. I finally have the sum I want Thanks to all of you and a particular thank you to Dave Jeanne "Dave Peterson" wrote: Columns E and F???? =sumproduct(--(text(e3:e276,"yyyymm")="200701"),--(f3:f276=True)) Adjust the ranges to match--but you can't use whole columns (except in xl2007). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html Jeannie wrote: Hi, I've been trying for 2 days to make this formula work. I have a spreadsheet that shows the employees that have been hired and the ones that have left the company. I need to count how many have arrived for each month. Column E has the date and Column F has the status (True for arrivals and False for departures). I need to have a formula that would combine: =COUNTIF(E3:E276, "TRUE") and =COUNTIF(E3:E276, "2007-01-*") (I'm not sure about the use of the wildcard in a formula) Any help would be greatly appreciated Thanks Jeanne -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
counting with multiple criteria and wildcards | Excel Worksheet Functions | |||
COUNTIF with wildcards | Excel Worksheet Functions | |||
countif wildcards? | Excel Discussion (Misc queries) | |||
Countif using format criteria not number criteria? | Excel Worksheet Functions | |||
Countif using format criteria....not number criteria? | Excel Worksheet Functions |