Find a value in a column and count text using a rolling date range

[JonesyCC]

Good morning

I'm trying to create a formula that will take a value from a cell and find the value in a row. Then count a number of time a text string appears in the column, using a rolling date range.


I was thinking I could use a HLOOKUP to find the value in the row, but then I think I need a nested COUNTIF statement to count the number of occurrences of my string "txt". But I need the range to be rolling so something like TODAY() - 365:TODAY() for the date range.

Or do I need to use a INDEX formula?

Any thoughts


Thanks
Dave

[Olly]

Put this cell F2:

Please Login or Register  to view this content.

You don't make clear the 'rolling date range', but the rows offset can be controlled by date parameters. How did you want to specify the date range?

[JonesyCC]

Hi thanks for your reply

Sorry I didn't make it very clears. What I'm looking for is the data selected in C2 is searched it the data table. Then every time a specific string "txt" or "ABS" which is identified in the formula is counted, because there will be different springs in the columns. But here's the catch I want the range to be dynamic based on today date going back a year or 365 days, so something like TODAY()-365:TODAY(). Only counting the "text" that appears within the last year. I'm ok if it has to be done in two formula's one to count the string within the dynamic date range and a Hlookup to reference the first formula. I'm not sure if that made it any clearer for you.

Thanks
Dave

[Olly]

Okay. Use:

Please Login or Register  to view this content.

[JonesyCC]

This is great, only one thing there will be different information in the columns like txt or abs etc. so the COUNTA wont work, can we use a COUNTIF? to specify the text string we are looking for?

Thanks
Dave

[JonesyCC]

Hi I just swapped out the COUNTA for a COUNTIF and included "txt". It work great. Thanks for your help, you rock!!!!

Thanks
Dave

[Olly]

This is great, only one thing there will be different information in the columns like txt or abs etc. so the COUNTA wont work, can we use a COUNTIF? to specify the text string we are looking for?

Thanks
Dave

Yes, you can

[Olly]

Hi I just swapped out the COUNTA for a COUNTIF and included "txt". It work great. Thanks for your help, you rock!!!!

Thanks
Dave

Excellent, glad it's helped.