0

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.

1
  • Hi and welcome. You are tracking multiple Countries and Platforms, so presumably the formula that you included is a sample (one of many). It is very hard to visualise your data and how these formula relate to that data. Would you please include some sample data in your question (whether by screenshot, table or spreadsheet); would you also include an example of a successful formula outcome. On a tangent you've tagged excel-online - why?
    – Tedinoz
    Oct 26 at 3:52
0

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

Data


Pivot Tables

Pivot


Date Filter setup

date filter

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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