SUMIFS with INDEX/MATCH and Dynamic ranges

Question

Here is the spreadsheet of data and desired result:

https://docs.google.com/spreadsheets/d/13tXrlZQK0bXlA2EhGA-CJEXAEdfxyvAWvqwflqty-GA/edit?usp=sharing

Basically, I wish to create a drop-down dependent result that is able to SUM the count (by month) and favorites (and potentially AVG the percentages) from a date range.

The only caveat is the date range is in D/M/YYYY format and I wish the drop-down to be month. So this will need an EOMONTH formula.

Answer 1

You can make use of helper columns.

The formulas that I used to achieve your results were:

count:

=SUMIFS(F5:F9,E5:E9,G11,D5:D9,F13)+SUMIFS(J5:J9,I5:I9,G11,D5:D9,F13)

favorite:

=SUMIFS(G5:G9,E5:E9,G11,D5:D9,F13)+SUMIFS(K5:K9,I5:I9,G11,D5:D9,F13)

pct. fav avg:

=IF(G11="Blue",AVERAGEIFS(L5:L9,I5:I9,G11,D5:D9,F13),AVERAGEIFS(H5:H9,E5:E9,G11,D5:D9,F13))

There are 2 helper columns used: color and month.

I'll attach the sample sheet I used for testing, you can just hide the helper columns.

Test Spreadsheet