Countif with dynamic headers

0
votes

Good afternoon! I'm trying to get a Countifs or Index Match statement to count the number of times a value occurs in another table. The example:

On my report sheet, Column A contains 10 different statuses, such as Green, Yellow, Red etc.; Row 1 contains six dates, such as 1/31/2015, 2/28/2015, etc. These dates are calculations. The last date references my date worksheet and the five other use EOMONTH to get the month end for the five prior months.

On my data table, I have 7 descriptive columns (such as Type, Make, Model, etc) and then we begin date columns: 1/31/2010 all the way to 7/31/2015. I add a new column each month (I know, I don't like it either, but unfortunately we don't have a time series database).

What I need to do is have a Countifs or Index Match that pulls the date from my report tab, goes and finds it in the header row of my tblTrends, and then counts all those statuses that are Green, and if it's a SUV (for example).

Thoughts?

Thx!!

G

1
excel

1 Answers

0
votes

At it's most basic, you'd want something akin to:

=COUNTIFS(TypeRange,"SUV",FirstMonth,Status)

So let's say your data table starts in column L, and the first date column is O:

=COUNTIFS($L$1:$L$100,"SUV",O$1:O$100,A$2)

As you drag this formula across the different dates, it will move the date reference over one to the next month.

If you need it to dynamically determine the date column, I'd recommend OFFSET, which dynamically select a range. However, note that OFFSET is a "volatile" formula, which means it re-calculates anytime a change is made anywhere in the file, which can lead to pretty slow load times if not used sparingly.

=COUNTIFS($L$2:$L$100,"SUV",OFFSET($N$2:$N$100,,MATCH(B$1,$O$1:$Z$1,0)),$A2)

The OFFSET starts on column N, because that's the first column before the columns we want (the date columns). The MATCH tells it how many columns to OFFSET from here.

If you're going to use this over a large amount of data, then you could avoid using the OFFSET formula by creating a dynamic table. This table would only contain data for the six months you're interested in, by utilizing INDEX/MATCH, and you could run your COUNTIFS off this table, instead, using the original, basic method I first described. I can go into detail if you're unsure what I mean.