CountIf referencing row number

[Noz2k]

So I have a formula at the moment which is this

=COUNTIFS(issuesdata!$C$2:$C$8479,">"&DATE(2011,3,15),issuesdata!$A$2:$A$8479,Issues!$A2)

Now the problem I have is that I want to add data where the date will not be 15/03/2011. I have created a new column (D) on 'Sheet1' where the date is converted to the format yyyy,mm,dd.

The row numbers in Sheet 1 correspond to the row number in issues. So Sheet1!D2 = "2011,03,15"

So I need to tweak my formula so that it users the appropriate cell as it's date reference rather than manually inputting it.

Thanks in advance

 

[jasonb75]

Maybe

=COUNTIFS(issuesdata!$C$2:$C$8479,">"&Sheet2!$D2,issuesdata!$A$2:$A$8479,Issues!$A2)

 

[Noz2k]

Definitely thought I had tried that. Think I tried it without the &, and as &Sheet1!D2:D87

Thanks

 

[Noz2k]

It's not quite giving me the correct answers.

For instance If I search through the data using ctrl+F I will find 4 values after that date, but my formula changes to 3.

If I leave it as Date(2011,3,15) it will find all 4. Any idea why this would be, the cell Sheet1!D2 reads 2011,3,15.

 

[jasonb75]

Try a test formula

=isnumber(Sheet1!D2)

If that returns false then it means your dates are not a valid excel format.

 

[Noz2k]

That returns True

 

[Noz2k]

Got it working, the problem was that whilst the referenced columns were formatted to 'yyyy,mm,dd' the columns that there were derived from also had Time values in them. So this was still effecting them.

Have used to trunc function to remove the time now, and all is working fine.

thanks for your help