Google sheets reference Text in Cell in Array Formula & Countif formula

Question

So, I have a Array Formula and Countif formula in place because I need to know the total of entries in a combination of ranges. I need to know the sum of the number of cells that have "X" country, "X" platform (Amazon, Alibaba, etc) and in a specific time range in another sheet.

The formula I'm using right now works, but since I need to gather this data every month, it means that every month I have to manually change the date in the formula.

This in on itself is Ok, it doesn't take too long, but since I need to change the date for every single Country cell it means I need to either manually change all the dates in each cell formula, or simply drag the formula to all cells and then change the country code I'm using.

Since I don't think there is a way to automatically change the date range, what I want is a way to instead of manually changing the Country code, that I could simply reference another cell's text that has that country code.

But for the life of me I can't find a way to do it without completely botching the whole formula. I've tried Indirect formula, ISTEXT, and even Concatenate.

This is my current formula:

=ArrayFormula(SUM(COUNTIFS(Reported!$A:$A,"US",Reported!$B:$B,{"Amazon"},Reported!$F:$F,">="&DATE(2021,9,1),Reported!$F:$F,"<="&DATE(2021,9,31))))  

Any help would be greatly appreaciated.

Answer 1

You haven't explained how your analysis is laid out and/or structured. However, it looks like a good candidate for a Pivot Table.

I've included two examples below to highlight the format as well as the effect of filtering by Dates.

In the second example (filtered by Date), it is a simple matter of entering the Date Range, and then applying the filter to the relevant cells.

---

Data

---

Pivot Tables

---

Date Filter setup