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

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

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

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

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

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