0

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]: Screenshot

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.

Screenshot

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!

1
  • Welcome to Web Applications Stack Exchange. Please edit your question and insert a table of sample data together with another table that shows your manually entered desired results. Also consider sharing a publicly editable sample spreadsheet. There is a blank sheet maker that lets you share safely. May 15, 2023 at 15:22

1 Answer 1

0

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))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.