How do I sum this??

[soccerdude44]

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]8/2/17[/TD]
[TD]8/25/17[/TD]
[TD]9/14/17[/TD]
[TD]9/25/17
[/TD]
[TD]10/1/17
[/TD]
[TD]10/29/17
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total Amount
[/TD]
[TD]500,000
[/TD]
[TD]250,000
[/TD]
[TD]150,000
[/TD]
[TD]1,000,000
[/TD]
[TD]750,000
[/TD]
[TD]250,000
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]By Location
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]IN
[/TD]
[TD]300,000
[/TD]
[TD]125,000
[/TD]
[TD]0
[/TD]
[TD]500,000
[/TD]
[TD]250,000
[/TD]
[TD]100,000
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]KY
[/TD]
[TD]100,000
[/TD]
[TD]125,000
[/TD]
[TD]150,000
[/TD]
[TD]250,000
[/TD]
[TD]250,000
[/TD]
[TD]0
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]OH
[/TD]
[TD]100,000
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]250,000
[/TD]
[TD]250,000
[/TD]
[TD]150,000
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

How would I create a sum function that would allow me to sum total amount by location, by month. For example Indiana has 425,000 in August, 500,000 in September, and 350,000 in October. I would need the formula to be able to sum the totals like that for each location.

Thanks for your help!

 

[Aladin Akyurek]

It's not clear what these exhibits are: Input? Output?

 

[Scott T]

Maybe

Excel 2010[TABLE="class: grid, width: 700"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]8/2/2017
[/TD]
[TD="align: right"]8/25/2017
[/TD]
[TD="align: right"]9/14/2017
[/TD]
[TD="align: right"]9/25/2017
[/TD]
[TD="align: right"]10/1/2017
[/TD]
[TD="align: right"]10/29/2017
[/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD]Total Amount
[/TD]
[TD="align: right"]500,000
[/TD]
[TD="align: right"]250,000
[/TD]
[TD="align: right"]150,000
[/TD]
[TD="align: right"]1,000,000
[/TD]
[TD="align: right"]750,000
[/TD]
[TD="align: right"]250,000
[/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]7
[/TD]
[TD]By Location
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]8
[/TD]
[TD]IN
[/TD]
[TD="align: right"]300,000
[/TD]
[TD="align: right"]125,000
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]500,000
[/TD]
[TD="align: right"]250,000
[/TD]
[TD="align: right"]100,000
[/TD]
[/TR]
[TR]
[TD="align: center"]9
[/TD]
[TD]KY
[/TD]
[TD="align: right"]100,000
[/TD]
[TD="align: right"]125,000
[/TD]
[TD="align: right"]150,000
[/TD]
[TD="align: right"]250,000
[/TD]
[TD="align: right"]250,000
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]10
[/TD]
[TD]OH
[/TD]
[TD="align: right"]100,000
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]250,000
[/TD]
[TD="align: right"]250,000
[/TD]
[TD="align: right"]150,000
[/TD]
[/TR]
[TR]
[TD="align: center"]11
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]12
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]13
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Aug
[/TD]
[TD="align: right"]Sep
[/TD]
[TD="align: right"]Oct
[/TD]
[TD="align: right"]Note that these are dates formatted to only show the month
[/TD]
[TD="align: right"]for example 8/1/17
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]14
[/TD]
[TD]IN
[/TD]
[TD="align: right"]425,000.00
[/TD]
[TD="align: right"]500,000.00
[/TD]
[TD="align: right"]350,000.00
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]15
[/TD]
[TD]KY
[/TD]
[TD="align: right"]225,000.00
[/TD]
[TD="align: right"]400,000.00
[/TD]
[TD="align: right"]250,000.00
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]16
[/TD]
[TD]OH
[/TD]
[TD="align: right"]100,000.00
[/TD]
[TD="align: right"]250,000.00
[/TD]
[TD="align: right"]400,000.00
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

Sheet5​

[TABLE="class: grid, width: 85%"]
<tbody>[TR]
[TD]
Array Formulas[TABLE="class: grid, width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TD="width: 10"]Cell
[/TD]
[TD="align: left"]Formula
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B14
[/TH]
[TD="align: left"]{=SUM(IF(MONTH(B$13)=MONTH($B$1:$G$1),INDEX($B$8:$G$10,MATCH($A14,$A$8:$A$10,0),0),0))}
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C14
[/TH]
[TD="align: left"]{=SUM(IF(MONTH(C$13)=MONTH($B$1:$G$1),INDEX($B$8:$G$10,MATCH($A14,$A$8:$A$10,0),0),0))}
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D14
[/TH]
[TD="align: left"]{=SUM(IF(MONTH(D$13)=MONTH($B$1:$G$1),INDEX($B$8:$G$10,MATCH($A14,$A$8:$A$10,0),0),0))}
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B15
[/TH]
[TD="align: left"]{=SUM(IF(MONTH(B$13)=MONTH($B$1:$G$1),INDEX($B$8:$G$10,MATCH($A15,$A$8:$A$10,0),0),0))}
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C15
[/TH]
[TD="align: left"]{=SUM(IF(MONTH(C$13)=MONTH($B$1:$G$1),INDEX($B$8:$G$10,MATCH($A15,$A$8:$A$10,0),0),0))}
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D15
[/TH]
[TD="align: left"]{=SUM(IF(MONTH(D$13)=MONTH($B$1:$G$1),INDEX($B$8:$G$10,MATCH($A15,$A$8:$A$10,0),0),0))}
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B16
[/TH]
[TD="align: left"]{=SUM(IF(MONTH(B$13)=MONTH($B$1:$G$1),INDEX($B$8:$G$10,MATCH($A16,$A$8:$A$10,0),0),0))}
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C16
[/TH]
[TD="align: left"]{=SUM(IF(MONTH(C$13)=MONTH($B$1:$G$1),INDEX($B$8:$G$10,MATCH($A16,$A$8:$A$10,0),0),0))}
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D16
[/TH]
[TD="align: left"]{=SUM(IF(MONTH(D$13)=MONTH($B$1:$G$1),INDEX($B$8:$G$10,MATCH($A16,$A$8:$A$10,0),0),0))}
[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[soccerdude44]

Hey Scott. Thanks for your response and sorry for first responding but I haven't had a chance to try this formula until now. I have tried your formula but it doesn't seem to be working for me. I check the formula multiple times to make sure it matches yours and it does but the formula keeps giving me 0's.

Do you know what might be causing this?? Could it be that I am using Excel 16 instead of 10??

 

[Scott T]

The only way I was able to return a zero is if the amounts in B8:G10 were text instead of numbers. The different versions of Excel should not matter.

 

[Aladin Akyurek]

@soccerdude44

Might be a good idea to answer post #2 .

 

[soccerdude44]

Hey Aladin. Those are inputs into an excel spreadsheet. Sorry about that.

 

[soccerdude44]

Hey Scott.

Thanks for the reply and I have now got it to return something other than zero. The only problem is that it is summing all the numbers based on what month is in the first cell and ignoring the ones after that. For example, from above, Cell B14 is summing all of the cells in row 8 from IN regardless of what month is stated based on what month is in cell B! So it looks like the lookup is working for what state it is in but not what month the transaction happened in??

 

[Scott T]

The formula is an array formula did you use CONTROL+SHIFT+ENTER?

 

[Aladin Akyurek]

Hey Aladin. Those are inputs into an excel spreadsheet. Sorry about that.

[TABLE="class: grid, width: 815"]
<tbody>[TR]
[TD][/TD]
[TD]8/2/2017[/TD]
[TD]8/25/2017[/TD]
[TD]9/14/2017[/TD]
[TD]9/25/2017[/TD]
[TD]10/1/2017[/TD]
[TD]10/29/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1-Aug-17[/TD]
[TD="align: right"]1-Sep-17[/TD]
[/TR]
[TR]
[TD]Total Amount[/TD]
[TD]500,000[/TD]
[TD]250,000[/TD]
[TD]150,000[/TD]
[TD]1,000,000[/TD]
[TD]750,000[/TD]
[TD]250,000[/TD]
[TD][/TD]
[TD]IN[/TD]
[TD="align: right"]425000[/TD]
[TD="align: right"]500000[/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]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/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]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]By Location[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]IN[/TD]
[TD]300,000[/TD]
[TD]125,000[/TD]
[TD]0[/TD]
[TD]500,000[/TD]
[TD]250,000[/TD]
[TD]100,000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]KY[/TD]
[TD]100,000[/TD]
[TD]125,000[/TD]
[TD]150,000[/TD]
[TD]250,000[/TD]
[TD]250,000[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]OH[/TD]
[TD]100,000[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]250,000[/TD]
[TD]250,000[/TD]
[TD]150,000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I2 = IN

J1: 8/1/2017

K1: 9/1/2017

In J2 just enter and copy across:

=SUMIFS(INDEX($B$2:$G$10,MATCH($I2,$A$2:$A$10,0),0),$B$1:$G$1,">="&J$1,$B$1:$G$1,"<="&EOMONTH(J$1,0))