Using a text function in the criteria of a COUNTIF

[AngelB]

Hi All

I'm using Excel 2003, SP2.

I have a workbook, in which the names of the days of the week run across the top row, and in the leftmost column the day is broken down into hour long segments such as:

0101-0200
0201-0300
0301-0400
0401-0500

What I would like to be able to do is where the rows and columns cross, pull data from a 'Raw Data' worksheet where the criteria are both met. e.g. In the raw data sheet I have a column for Weekday Name and another for Time of Day. I want to count the number of rows where a particular day and time occur and put that count into my new worksheet. I want to do this using the SUMPRODUCT function, but firstly I want to get the COUNTIF section working on the time column. I'm having trouble chopping up my 'time' cells to tell the function what range of times to look at.

=COUNTIF('Raw Data'!E2:E65535,">=LEFT(A6,4)") - COUNTIF('Raw Data'!E2:E65535,">MID(A6,6,4)")

I have been trying to use the above code to return the correct number of rows but it always returns zero even when the criteria are met. Can someone point out where I am going wrong, or maybe offer a different way of tackling the problem?

Many Thanks


Geraint

The lights are on but nobody's home, my elevator doesn't go to the top. I'm not playing with a full deck, I've lost my marbles. Barenaked Ladies - Crazy

 

[xlbo]

Try this Geraint

=COUNTIF('Raw Data'!E2:E65535,">=" & LEFT(A6,4)) - COUNTIF('Raw Data'!E2:E65535,">" & MID(A6,6,4))


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[AngelB]

Thanks for that Geoff. Tried your suggestion, but still getting zeroes. There must be something else I'm missing. Will have another look and get come back if nothing works!


Geraint

The lights are on but nobody's home, my elevator doesn't go to the top. I'm not playing with a full deck, I've lost my marbles. Barenaked Ladies - Crazy

 

[GlennUK]

What are the entries like in 'Raw Data'!E2:E65535? Are they strings? Are they Excel times? Are they integers? Are they date & time combined?

Note: for any date and time entries the cell formatting is not important ... it is the fact that the cell entries are true Excel date/time serial numbers that is important. Telling us whether a time is on it's own or part of a date/time entry is also important.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.