GoogleSheets - How To CountIF within Date Range

Question

Good evening, Thank you in advance for any assistance you can provide.

I am trying to sum (countif) the total amount of matching items from one sheet, if they match a certain timeframe range (e.g. one week, one month, ALL) on the same sheet, to the matching item on the recall sheet.

EXAMPLE: This is the sheet I want to collect the countif totals [let's call this SHEET 1]:

And this is the separate sheet [let's call it SHEET 2] collecting responses that I want to tally according to their date and equaling the value on [SHEET 1] in column A.

I need the SHEET 1 formula for each AREA to search say B:K on SHEET 2 and total the amount for each AREA on SHEET 1 for the last 7 days, last month and all time.

I was able to make something like this work before but now trying to piece together code and formula from multiple tutorials I am lost!

Please help thank you so much!

Answer 1

The ISBETWEEN function will allow you to determine which entries fall within the date range.

=ISBETWEEN(E:E,TODAY()-7, TODAY())   //  Last 7 Days
=ISBETWEEN(E:E,TODAY()-30,TODAY())   //  Last 30 Days

Formula

FILTER is used with ISBETWEEN to ensure the rows fall within the date range. Then COUNTIF can count the occurrences of the value in A2

=COUNTIF(FILTER(F:H,ISBETWEEN(E:E,TODAY()-7,TODAY())),A2)

Alternatively, adding ARRAYFORMULA will make this a single-cell formula, avoiding the need to copy and paste the formula down the column. This will allow editing the formula for all the rows in the same place.

=ARRAYFORMULA(COUNTIF(FILTER(F:H,ISBETWEEN(E:E,TODAY()-7,TODAY())),A2:A9))