Single column countif with moving range based on today's date

1. Single column countif with moving range based on today's date

Good morning

I'm trying to create a COUNTIF statement that will count every time a string "ABS" appears in a date range. But I need the date range to roll based on today's date, so something like this = COUNTIF(TODAY()-365:TODAY(),"ABS"). I know the range is invalid, I'm not sure the proper syntax to make this work. So I have a column with all the dates listed (A4:A734), and a row with names (B1:GT1). I need a formula in each column to count every time ABS appears in that same column within a year from todays date.

I have attached the worksheet.Scheduling workbook.xlsx

2. Re: Single column countif with moving range based on today's date

I didn't look at your file (too big!)

Maybe something like this...

=COUNTIFS(A4:A734,">="&EDATE(TODAY(),-12),B4:B734,"ABS")

3. Re: Single column countif with moving range based on today's date

Try
=COUNTIFS(B\$4:B\$734,"abs",\$A\$4:\$A\$734,">="&TODAY()-365,\$A\$4:\$A\$734,"<="&TODAY())

Although, today-365 may not be the best way to go back a year...
Some years have 366 days..

This might be more accurate
=COUNTIFS(B\$4:B\$734,"abs",\$A\$4:\$A\$734,">="&EDATE(TODAY(),-12),\$A\$4:\$A\$734,"<="&TODAY())

4. Re: Single column countif with moving range based on today's date

Thanks this works great.

5. Re: Single column countif with moving range based on today's date

You're welcome. We appreciate the feedback!

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1