CountIF comparison to date entries with range.

[crsouser]

Hi,
I have a large number of entries on one tab of a spreadsheet and I need to perform some trending on the data. I know how to do it in SQL, but having some difficulty doing it in Excel and I don't have the SQL option.

My data looks something like the following on one tab:
A, B, C, D
5088,07/18/2011 03:23:23, Entity 1, 25
5088,07/18/2011 03:23:25, Entity 1, 25
5088,07/18/2011 03:23:25, Entity 2, 25
5088,07/18/2011 03:23:27, Entity 1, 42
5088,07/18/2011 03:23:25, Entity 2, 25
5088,07/19/2011 03:23:25, Entity 3, 25
5088,07/19/2011 03:23:25, Entity 3, 25
5088,07/19/2011 03:23:25, Entity 1, 25
5088,07/20/2011 03:23:25, Entity 2, 25
5088,07/21/2011 03:23:26, Entity 2, 25
5088,07/22/2011 03:23:27, Entity 2, 25
5088,07/22/2011 03:23:28, Entity 2, 25

What I am trying to do on another tab is get a result that looks something like the following:

07/18/2011, Entity 1, 25, 2
07/18/2011, Entity 1, 42, 1
07/18/2011, Entity 2, 25, 1
07/19/2011, Entity 1, 25, 1
07/19/2011, Entity 3, 25, 2
07/20/2011, Entity 2, 25, 1
07/21/2011, Entity 2, 25, 1
07/22/2011, Entity 2, 25, 2


Can I get some guidance on how to do this, as my formula below persistently always comes up with 0s even when just trying to do a general count on a date range for all entities.

=COUNTIF(25s!B1:B6619,"*"&A2&"*")

On my second tab where I have this formula I have a row for each date range where A2 is 7/18/2011 for example.

Thanks,
Christopher

 

[tweedle]

where A2 is 7/18/2011 for example

Make the entries here formatted as text (add a leading ' ).
Excel is likely looking at them as numerics which won't match in a Like scenario.


[Could also run your source data through 'Text To Columns' and generate Pivot Tables]