Excel Function 1

[brunnle2]

I would like to automate some reporting I do with Excel. In one cell I need to count the number of rows in another worksheet that match that date where I am recording the data. Make Sense? For example. When I report on calls logged on 28/06/05 I need to count rows in the other worksheet where column 'x' starts with 28/06/05.

 

[DPlank]

Suggest you look into use of the COUNTIF function. This works best if you're using a single workbook though, as using it across different workbooks requires you to have both books open when the formula recalculates.

If you're using multiple books, look into nesting an IF inside a SUM function.

HTH

Cheers,
Dave

Probably the only Test Analyst Manager on Tek-Tips...therefore whatever it was that went wrong, I'm to blame...

animadverto vos in Abyssus!

Take a look at Forum1393!

 

[AnotherHiggins]

.... I need to count rows in the other worksheet where column 'x' starts with 28/06/05.

What else do the cells contain? Is there any text, or is it just the date and time?

[tt]_____
[blue]-John[/blue]
[/tt][red]"I'm against picketing, but I don't know how to show it."[/red]
-Mitch Hedberg

Help us help you. Please read FAQ181-2886 before posting.

 

[brunnle2]

The cells also contain the time. When data has been exported from our Database they are in the format dd/mm/yyyy hh:mm:ss

 

[brunnle2]

Still trying.... HELP ME!!

At the moment I have =COUNTIF(FF!D,"<=$A5") in my cell where I wish to count all cells where the date is less than what is in cell A5. I get 0, but am expecting 29. The range FF!D contains the format dd/mm/yyyy hh:mm:ss. In A5 is dd-mmm-yy.

I do hope I am making sense.

 

[xlbo]

try this:
=COUNTIF(FF!D,"<=" & $A5)



Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[brunnle2]

Great that worked! - Thanks Geoff... One more problem though I need to add a ">=" into the function, so that It counts only one date. I Tried: =COUNTIF(FF!D,"<=" & $A5 ">=" & $A4)
But this gives an error.

 

[xlbo]

can't do multiple criteria in a COUNTIF

If you want to count on one date though, why not just use:

=COUNTIF(FF!D,"=" & $A5)

???

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[brunnle2]

I did try that, but the value is 0. I guess this is because of the cell formatting. FF!D is dd/mm/yyyy hh:mm:ss and A5 is dd-mmm-yy. So they will not "=" to one another. I'm sure there is a way round it. Hopefully you might know?

Tvm,
Leighton.

 

[xlbo]

And do your dates in FF!DD have actual times on them or are they all 00:00:00 ??

If they are then just change the formatting and it should work. formatting on target must match formatting on teh cells being searched

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[brunnle2]

They have actual times. This is the Date and time that a call was made in the system. It is required to report on Service Levels. Can I add a string reference to the
"=" so that it looks at only the first chars, i.e. dd/mm/yyyy. Or is there an easy way I can split my exported cells that have date and time. Splitting these into two columns may be the easiest way. Except I don't know how to do this.

TVM,
Leighton.

 

[xlbo]

ok - 2 ways to go here

1: as you have already thought - split your dates & times
easiest way to do this for the dates is to use the INT() function on them. This will turn them all into dates with no times

2: you can go about this as you have started already

=COUNTIF(FF!D,"<=" & $A5) - COUNTIF(FF!D,"<=" & $A4)

where A4 = TheDate 00:00:00
A5 = TheDate 23:59:59

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question