Using SumIFS, Index, and Match two match two fields and sum repeating values?

[Rhystic]

Hello, I'm in a predicament. I have an excel workbook, where the data comes in a sheet named Attendance, organized as such:
[TABLE="width: 1537"]
<colgroup><col><col><col><col><col span="2"><col span="2"><col span="5"><col></colgroup><tbody>[TR]
[TD]
#


2
[/TD]
[TD]
City


nyc[/TD]
[TD]
Date Range


13-16 Aug 2015[/TD]
[TD]
Sport


Volleyball[/TD]
[TD]
Teams


6[/TD]
[TD]
Athlete Volunteer

22[/TD]
[TD]
Athlete Youth

80[/TD]
[TD]
Audience Volunteer

100[/TD]
[TD]
Audience Community

500
[/TD]
[TD]
Total Athletes

102[/TD]
[TD]
Total Audience

600[/TD]
[TD]
Speakers


2[/TD]
[TD]



AUG[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]newark[/TD]
[TD]13-16 Aug 2015[/TD]
[TD]Soccer[/TD]
[TD]4[/TD]
[TD]7[/TD]
[TD]39[/TD]
[TD]18[/TD]
[TD]170[/TD]
[TD]46[/TD]
[TD]188[/TD]
[TD]1[/TD]
[TD]AUG[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]boston[/TD]
[TD]15-17 Aug 2015[/TD]
[TD]Soccer[/TD]
[TD]4[/TD]
[TD]18[/TD]
[TD]54[/TD]
[TD]45[/TD]
[TD]850[/TD]
[TD]72[/TD]
[TD]895[/TD]
[TD]4[/TD]
[TD]AUG[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]newark[/TD]
[TD]17-20 Aug 2015[/TD]
[TD]Football[/TD]
[TD]4[/TD]
[TD]8[/TD]
[TD]46[/TD]
[TD]30[/TD]
[TD]340[/TD]
[TD]54[/TD]
[TD]370[/TD]
[TD]5[/TD]
[TD]AUG[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]boston[/TD]
[TD]16-18 Aug 2015[/TD]
[TD]Football[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]29[/TD]
[TD]26[/TD]
[TD]230[/TD]
[TD]34[/TD]
[TD]256[/TD]
[TD]5[/TD]
[TD]AUG[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]newark[/TD]
[TD]19-23 Aug 2015[/TD]
[TD]Volleyball[/TD]
[TD]6[/TD]
[TD]9[/TD]
[TD]61[/TD]
[TD]65[/TD]
[TD]430[/TD]
[TD]70[/TD]
[TD]495[/TD]
[TD]6[/TD]
[TD]AUG[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]nyc[/TD]
[TD]10-24 Aug 2015[/TD]
[TD]baseball[/TD]
[TD]8[/TD]
[TD]22[/TD]
[TD]66[/TD]
[TD]220[/TD]
[TD]580[/TD]
[TD]88[/TD]
[TD]800[/TD]
[TD]5[/TD]
[TD]AUG[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Baltimore[/TD]
[TD]10-25 Aug 2015[/TD]
[TD]volleyball[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]99[/TD]
[TD]160[/TD]
[TD]640[/TD]
[TD]110[/TD]
[TD]800[/TD]
[TD]6[/TD]
[TD]AUG[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

This is for one month, in actuality there are more columns with previous months. In a new sheet which I call rollingchart I have a chart :

[TABLE="width: 261"]
<colgroup><col span="2"><col><col></colgroup><tbody>[TR]
[TD]AUG[/TD]
[TD]NYC[/TD]
[TD]Baltimore[/TD]
[TD] Boston[/TD]
[/TR]
[TR]
[TD]Spectators [/TD]
[TD]600[/TD]
[TD]800[/TD]
[TD]895[/TD]
[/TR]
[TR]
[TD]Athletes[/TD]
[TD]40[/TD]
[TD]110[/TD]
[TD]72[/TD]
[/TR]
</tbody>[/TABLE]

What I utilize to get these numbers currently is: =INDEX(Attendance!$A$1:$N$500,MATCH(1,(Attendance!$B$1:$B$500=B1)*(Attendance!$M$1:$M$500=$A$1),0),11) [for NYC yielding 600 ~cell B2]

Essentially I match the City AND the Month to return the values. This works fine when the city (e.g. Baltimore) is only occuring once in the month, however when the city occurs more than once such as Newark and NYC, I would like the formula to Sum the values of audience and athletes respectively. I've tried a Sumifs but I can't figure out how to make it only sum for the specific month (it sums the entire set of months from Jan to august). Is what I'm asking possible ?

 

[Mackers]

Hi Rhystic

Sounds like you want a sumproduct (or a SUM(IF()) as an array function). Essentially anything involving summing or counting can be done with a sumproduct, and it is a bit more flexible than its more efficient cousins, SUMIFS and COUNTIFS, which can't accept array inputs (so you couldn't do MONTH() over a date range as we do below):

=SUMPRODUCT((MONTH(DateColumn)=DesiredMonthNumber)*(CityColumn=DesiredCity)*TotalAudienceColumn)

You can change anything you like but the formatting is the same - you highlight the column, say you want it to equal something (this can be a cell or hard coded) or leave it as-is for the sum column (in this case the audience numbers), then multiply it by the other columns.

Hope that helps

Mackers