Count occurrences of string in range, based on date range and other criteria

Hi All,

Can anyone help with a formula I'm struggling with? I've spent most of the day googling, but not got much closer to a solution.

I'm trying to count the number of times a text string occurs in a multi-row table, based upon a date range in a separate row. To complicate matters there are a couple of other criteria that need to be met.

So far, I've used COUNTIFS to create a helper table at the top of my sheet, that runs the full length of my data. The helper table counts the number of times the required strings occur in each column, where a criterion is met in another column. Then on a separate sheet I've used SUMIFS formula to add up only those values that fall within my required date range.

I'm hoping to be able to return a value without using the helper table, so I remove the chance of someone overwriting the calculations on that sheet and also because I have the table running out to five years and I think this is what is causing the sheet to freeze when I try and insert a new line.

Here's a simplified example of what I'm trying to do. I'm hoping to be able to be able to use a single formula in the blue shaded cell to be able to return the answer.

Book 1.JPG

I don't know if I've been able to explain what I'm trying to do very well. I tried to attach my original file but wasn't able to.

Thanks in advance for reading!


Neil

Unfortunately the attachment icon doesn't work at the moment (it hasn't worked for years, and despite our repeatedly asking the technical team who own the forum to fix it, they can't be bothered to do so), so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

I can't see anything like your image in that workbook. Can you tell us where we should be looking? Have you mocked up some expected results in the workbook to show us what you are after?