index match for table and partial cell contents

Hi there

I have a spreadsheet (named "checks" ) which goes from h8 to zz150

In column M, I have various comments including "No Records"

In Column R I have comments such as " Lee 01/10/2016"

On a separate tab, (sheet called "sheet3) I have a table

Along the top in row 3 I have named the columns, such as "No records"

In Column C I have dates, such as 01/10/2016

What I need is a formula to count the occurences whereby it cross references both "No records" and the date.

((As above with the date, the cell also contains a name, so it needs to be a partial search on the date)

Can anyone help please?

Please post a small sample file showing expected results.

To Attach a File:

1. Click "Go Advanced"
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

PLease see attached. I have colour coded to hopefully aide what I am trying to do.

Try

In I7

=SUMPRODUCT(($D$8:$D$15=I$6)*(DATEVALUE(RIGHT($E$8:$E$15,10))=$H7))

Copy across and down

Many thanks

Hi there. Sorry to reopen but I notice it does not allow blanks. Can you advise to allow for this please

By blanks ... which cells?

I have found if the date cell is blank then it returns #value in the count

Try

=SUMPRODUCT(($D$8:$D$15=I$6)*(IF($E$8:$E$15<>"",(DATEVALUE(RIGHT($E$8:$E$15,10))=$H7),0)))

Enter with Ctrl+Shift+Enter

Lovely, thanks , works perfectly!

Originally Posted by JohnTopley

Try

=SUMPRODUCT(($D$8:$D$15=I$6)*(IF($E$8:$E$15<>"",(DATEVALUE(RIGHT($E$8:$E$15,10))=$H7),0)))

Enter with Ctrl+Shift+Enter