Count partial match based on single records

Hi all.

I'm hoping this one is straightforward.....

After trying nested COUNTIFS, IF(ISNUMBER(SEARCH and SUMPRODUCT, I'm still not getting the results I quite want.

I have a spreadsheet of raw data containing a row per student and a field that contains multiple timestamps e.g. 2018-09-07 01:00:00,2018-09-08 01:00:00,2018-09-21 00:00:00 - annoying, but unfortunately this is the only format available.

I then have another sheet that will count the number of occurances a date appears in the timestamp, by each student:

working.JPG

But I am struggling with the formula to do the count.

I have tried the following two formulas, but neither work exactly how I need:
=IF(Download!$A:$A=$A4,,IF(ISNUMBER(SEARCH(D$1,Download!$AF4)),"1", ""))
=SUMPRODUCT((Download!$A:$A=Working!$A18)*(--ISNUMBER(SEARCH(D$1,Download!$AF18))))

Is there a simpler/more elegant way of working this out?

Many thanks in advance.

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Hi there
if a date appears twice or more for a particular student, does the cell for that particular students timestamp change to 2 and so on?

Hi José. Thanks for the advice, I've added attached a workbook. Apologies for the image.

Brian - Although it is highly unlikely that would happen, no I wouldn't want the count to increase to 2. For it to only show a "1" if one or more occurrences appeared.

Thanks.

Try this in AFTER!D2 and copy forward

Formula:

Please Login or Register  to view this content.

v nice jose.
v nice.

Yes Jose, that works! Thank you so much.

Out of interest, how does it know to look at the data for that particular student? Is it something to do with BEFORE?

Originally Posted by sted999

...
Out of interest, how does it know to look at the data for that particular student? Is it something to do with BEFORE?

Yes. I assume that the same student is in the same row in both sheets.

I'm happy to have helped.

Do not forget to mark the thread as SOLVED.