Countif with dates for vs 2003

[Kay]

Hello all,

I have looked through the archives and have seen several formulas that
should work in my spreadsheet, but return either a 0 or the wrong number.
The dates are entered using 1/6/08 and formatted to appear as Jan-08. I want
to count the number of cells that have Jan as a date, etc.

I have tried the following:
=COUNTIF(Data!F2:F65536,">=1/1/2008") -COUNTIF(Data!F2:F65536,"<=1/31/2008")
The result counts all cells rather than the 50 it should be

=SUM(COUNTIF(F2:F65536,{">12/31/08","<=1/1/09"})) the result is 0

=SUMPRODUCT((MONTH(Data!F2:F65536)=1),(YEAR(Data!F2:F65536)=2008)) the
result is 0

I am stumped.

Would really appreciate the help!

 

[Dave Peterson]

How about:

=COUNTIF(Data!F2:F65536,">=date(2008,1,1))
-COUNTIF(Data!F2:F65536,"<=date(2008,1,31))

Or
=COUNTIF(Data!F:F,">=date(2008,1,1))
-COUNTIF(Data!F:F,"<=date(2008,1,31))

(I bet you don't have a date in F1)

=SUMPRODUCT(--(MONTH(Data!F2:F65536)=1),--(YEAR(Data!F2:F65536)=2008))
or
=SUMPRODUCT(text(Data!F2:F65536,"yyyymm")="200801")

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 here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

 

[Glenn]

Hello all,

I have looked through the archives and have seen several formulas that
should work in my spreadsheet, but return either a 0 or the wrong number.
The dates are entered using 1/6/08 and formatted to appear as Jan-08. I want
to count the number of cells that have Jan as a date, etc.

I have tried the following:
=COUNTIF(Data!F2:F65536,">=1/1/2008") -COUNTIF(Data!F2:F65536,"<=1/31/2008")
The result counts all cells rather than the 50 it should be


=SUM(COUNTIF(F2:F65536,{">12/31/08","<=1/1/09"})) the result is 0

=SUMPRODUCT((MONTH(Data!F2:F65536)=1),(YEAR(Data!F2:F65536)=2008)) the
result is 0

I am stumped.

Would really appreciate the help!

 

[Glenn]

Shouldn't the "<=" also be ">="?

Also, as pointed out by Dave Peterson, use DATE(year,month,day).

= COUNTIF(Data!F2:F65536,">="&DATE(2008,1,1)) -
COUNTIF(Data!F2:F65536,">="&DATE(2008,1,31))

 

[Kay]

DAve,

The first sumproduct example you gave me worked fine so thanks so much, but
I would l ike to understand why the examples I sent you did not work.

 

[Dave Peterson]

I'd guess that stuff like this:
1/1/08
is seen as
1 divided by 1 divided by 8

Not as a real date.

 

[Kay]

DAve,
Your explanation makes sense. thanks, but let me pick your genius brain one
more time. If I find my answer using your solution of how many people were
hired as contractors,
=SUMPRODUCT(--(MONTH(Data!F2:F65536)=1),--(YEAR(Data!F2:F65536)=2008)) how
would i then find out how many of those were converted to permanent employees
in the same date range. They might be hired as a contractor and stay that
way for several months or they could be permanently hired within the same
month. I tried
=(SUMPRODUCT(--(MONTH(Data!F2:F65536)=1),--(YEAR(Data!F2:F65536)=2008))-SUMPRODUCT(--(MONTH(Data!G2:G65536)=1),--(YEAR(Data!G2:G65536)=2008)))
but get the wrong answer.

 

[Dave Peterson]

First, I would check to see if this variation of your formula:
=SUMPRODUCT(--(text(Data!F2:F65536,"yyyymm")="200801"))
counted the number of contractors hired in Jan of 2008.

And this
=SUMPRODUCT(--(text(data!G2:G65536,"yyyymm")="200801"))
counted the number of permanent employees that started in Jan of 2008.

I think this formula is easier to read, but will be equivalent to each portion
in your original formula.

And as an aside, if you can pick a smaller number of rows to check, your
calculation times will be better.

But I'm not sure doing the subtraction will get you what you want. If I was
hired as a contractor in August of 1967 and made permanent in January of 2008,
then I'll be counted in that second formula.

If you want to limit your count to just the people hired as contractors in Jan
2008 and converted to permanent employees in Jan of 2008, I think just adding
more conditions to the =sumproduct() should work:

=SUMPRODUCT(--(text(Data!F2:F65536,"yyyymm")="200801"),
--(text(data!G2:G65536,"yyyymm")="200801"))

Both of these conditions have to be true for it to be counted.

DAve,
Your explanation makes sense. thanks, but let me pick your genius brain one
more time. If I find my answer using your solution of how many people were
hired as contractors,
=SUMPRODUCT(--(MONTH(Data!F2:F65536)=1),--(YEAR(Data!F2:F65536)=2008)) how
would i then find out how many of those were converted to permanent employees
in the same date range. They might be hired as a contractor and stay that
way for several months or they could be permanently hired within the same
month. I tried
=(SUMPRODUCT(--(MONTH(Data!F2:F65536)=1),--(YEAR(Data!F2:F65536)=2008))-SUMPRODUCT(--(MONTH(Data!G2:G65536)=1),--(YEAR(Data!G2:G65536)=2008)))
but get the wrong answer.

 

[Kay]

Dave,

Once again thanks. That did simplify the problem because when I tried
adding other conditions to the first version, it was just to complex.

I really appreciate it.