CountIf where criteria is Less Than...

[Guest]

Hello again

Same sheet, trying to evaluate tasks that are stating before stated date. Using CountIf formula, but the < function doesn't seem to cut it:

=COUNTIF(Task_Table1!$A$2:$A$2000,< A1

And the envelope please...

 

[Michael J. Malinsky]

The < function should cut it fine, but the COUNTIF function requires quotes
when something other than a number is used, so try:

=COUNTIF(Task_Table1!$A$2:$A$2000,"<"&A1)

HTH
--
Michael J. Malinsky
Pittsburgh, PA

"I was gratified to be able to answer promptly,
and I did. I said I didn't know." -- Mark Twain

Hello again,

Same sheet, trying to evaluate tasks that are stating before stated date.

Using CountIf formula, but the < function doesn't seem to cut it:

 

[Frank Kabel]

Hi Chris
try
=COUNTIF(Task_Table1!$A$2:$A$2000,"<" & A1)

 

[JE McGimpsey]

Try:

=COUNTIF(Task_Table1!$A$2:$A$2000,"<" & A1)

 

[Bob Phillips]

Hi Chris,

Try

=COUNTIF(Task_Table1!$A$2:$A$2000,"< "&A1)


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Hello again,

Same sheet, trying to evaluate tasks that are stating before stated date.

Using CountIf formula, but the < function doesn't seem to cut it:

 

[Guest]

After reading this, maybe I should clarify a little more..

Column A has dates on sheet Task_Table1. I input a date onto worksheet Task Report, cell A1. I was hoping to have the countif formula return count of values less than the date I entered into cell A1.

 

[Bob Phillips]

Did you try it. AFAICS this is what it is doing.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

After reading this, maybe I should clarify a little more...

Column A has dates on sheet Task_Table1. I input a date onto worksheet

Task Report, cell A1. I was hoping to have the countif formula return count
of values less than the date I entered into cell A1.

 

[Guest]

Beauty it works. I forgot the ampersand

 

[Guest]

Okay so the countif less than work, and my previous wuestion was how to use AND function, so of course now: How would you use the SUMPRODUCT ... AND ... the Less Than. I've tried

=SUMPRODUCT((Task_Table1!$A$2:$A$2000="CMDT")*(Task_Table1!$E$2:$E$2000 "<"&A1)

and it errors. I'm trying to capture the late tasks for each individual project.

Thanks a bunch for eveyones participation in this

 

[Frank Kabel]

Hi
try
=SUMPRODUCT((Task_Table1!$A$2:$A$2000="CMDT")*(Task_Table1!$E$2:$E$2000
<A1))

 

[Guest]

It just kills me how simple these things are sometimes. When it did not work that wya the first time...I just assumed it wouldn't this time

Thanks Frank

 

[Guest]

Frank

I have a question: I use the formula and it work fine, except it seems to be read the date format incorrectly. I enter the date 03/04/04, and it seems to miss the dates that are 2003, like 12/15/03. I reformatted to place the year first, yy/mm/dd, but that doesn't seem to make a difference. Any ideas?

 

[Frank Kabel]

Hi Chris
are both cells formated as date and contain real dates (and not text
values)?

 

[Guest]

That did the trick, once I reformatted the whole range, they all popped up correctly

Thanks again