Sum if distinct value with multiple criteria

[nerdalert22]

I've been racking my brain all day on this one and desperately need someone to swoop in and give me the answer. Data is set up as follows:

[TABLE="width: 228"]
<tbody>[TR]
[TD]SSN[/TD]
[TD]$Dollars[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]$10[/TD]
[TD]10/10/2018[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]$10[/TD]
[TD]10/12/2018[/TD]
[/TR]
[TR]
[TD]456[/TD]
[TD][/TD]
[TD]10/14/2018[/TD]
[/TR]
[TR]
[TD]456[/TD]
[TD]$20[/TD]
[TD]10/16/2018[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD][/TD]
[TD]11/10/2018[/TD]
[/TR]
[TR]
[TD]789[/TD]
[TD]$10[/TD]
[TD]11/12/2018[/TD]
[/TR]
[TR]
[TD]1011[/TD]
[TD]$50[/TD]
[TD]11/14/2018[/TD]
[/TR]
[TR]
[TD]789[/TD]
[TD]$10[/TD]
[TD]11/16/2018[/TD]
[/TR]
[TR]
[TD]456[/TD]
[TD]$40[/TD]
[TD]12/10/2018[/TD]
[/TR]
[TR]
[TD]789[/TD]
[TD]$40[/TD]
[TD]12/12/2018[/TD]
[/TR]
[TR]
[TD]1011[/TD]
[TD]$30[/TD]
[TD]12/14/2018[/TD]
[/TR]
[TR]
[TD]789[/TD]
[TD]$40[/TD]
[TD]12/16/2018[/TD]
[/TR]
</tbody>[/TABLE]


I need to dynamically (data will routinely be added to this list) sum the dollars per month for unique ssn (i.e. if a ssn appears twice in a month, only sum it once) - I need to reference the date parameter in the equation so that it is dynamic in that way too (all months of data are in one sheet). So the right answer is this:

Oct-2018 $30
Nov-2018 $60
Dec-2018 $110


I've seen lots of equations like this today, so I tried it create it for my dataset, but it's not giving the right answer

=SUM(IF(FREQUENCY(IF((C2:C13>=G3)*(C2:C13< G4),match(a2:a13,a2:a13,0)),row(a2:a13)-row(a2)+1),b2:b13))
(where G3=10/1/18 and G4=11/1/18), but it is giving me $10 and it should be $30. I am not attached to this equation if it's all wrong! I just need something that works, and I would like to avoid a helper column, and a pivot table is not a solution for me.

THANK YOU FOR ANY HELP EXCEL MASTERS!!




</g4),match(a2:a13,a2:a13,0)),row(a2:a13)-row(a2)+1),b2:b13))

 

[DanteAmor]

These months on the sheet you have them as text

Oct-2018
Nov-2018
Dec-2018

Ok, I saw:
where G3=10/1/18 and G4=11/1/18

Give me some time to review it

 

[nerdalert22]

Sorry, I was just typing it that way to summarize to you all, in the sheet the cells referenced are date correct format (10/1/2018, etc)

 

[DanteAmor]

Try this:

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:78.89px;" /><col style="width:45.62px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#92d050; color:#333333; font-weight:bold; font-family:Verdana; font-size:9pt; text-align:center; ">SSN</td><td style="background-color:#92d050; color:#333333; font-weight:bold; font-family:Verdana; font-size:9pt; text-align:center; ">$Dollars</td><td style="background-color:#92d050; color:#333333; font-weight:bold; font-family:Verdana; font-size:9pt; text-align:center; ">Date</td><td style="background-color:#92d050; font-weight:bold; "> </td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">MONTH</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">RESULT</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">123</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">$10</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">10/10/2019</td><td > </td><td style="text-align:right; ">01/10/2019</td><td style="text-align:right; ">30</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">123</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">$10</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">12/10/2019</td><td > </td><td style="text-align:right; ">01/11/2019</td><td style="text-align:right; ">60</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">456</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; "> </td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">14/10/2019</td><td > </td><td style="text-align:right; ">01/12/2019</td><td style="text-align:right; ">110</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">456</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">$20</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">16/10/2019</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">123</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; "> </td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">10/11/2019</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">789</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">$10</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">12/11/2019</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">1011</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">$50</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">14/11/2019</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">789</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">$10</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">16/11/2019</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">456</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">$40</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">10/12/2019</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">789</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">$40</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">12/12/2019</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">1011</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">$30</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">14/12/2019</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">789</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">$40</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">16/12/2019</td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >F2</td><td >=SUMPRODUCT((MONTH($C$2:$C$13)=MONTH(E2))*(MATCH($A$2:$A$13&$B$2:$B$13,$A$2:$A$13&$B$2:$B$13,0)=ROW(INDEX($B$2:$B$13,0,0))-1)*($B$2:$B$13))</td></tr></table></td></tr></table> <br /><br />

 

[nerdalert22]

Thank you very much for your reply. I'm not sure it is working - for instance, if you change the date on row 13 to be in October, it does not seem to change the value in F2.

 

[DanteAmor]

We must use an auxiliary column, in cell D2 goes an array formula and copies it down.

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:78.89px;" /><col style="width:45.62px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#92d050; color:#333333; font-weight:bold; font-family:Verdana; font-size:9pt; text-align:center; ">SSN</td><td style="background-color:#92d050; color:#333333; font-weight:bold; font-family:Verdana; font-size:9pt; text-align:center; ">$Dollars</td><td style="background-color:#92d050; color:#333333; font-weight:bold; font-family:Verdana; font-size:9pt; text-align:center; ">Date</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">AUX</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">MONTH</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">RESULT</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">123</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">$10</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">10/10/2019</td><td style="text-align:right; ">1</td><td style="text-align:right; ">01/10/2019</td><td style="text-align:right; ">70</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">123</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">$10</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">12/10/2019</td><td style="text-align:right; ">2</td><td style="text-align:right; ">01/11/2019</td><td style="text-align:right; ">55</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">456</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; "> </td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">14/10/2019</td><td style="text-align:right; ">0</td><td style="text-align:right; ">01/12/2019</td><td style="text-align:right; ">70</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">456</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">$20</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">16/10/2019</td><td style="text-align:right; ">1</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">456</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">60</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">10/10/2019</td><td style="text-align:right; ">2</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">789</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">$10</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">12/11/2019</td><td style="text-align:right; ">1</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">1011</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">$50</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">10/11/2019</td><td style="text-align:right; ">1</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">123</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">$5</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">16/10/2019</td><td style="text-align:right; ">3</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">456</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">$40</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">10/12/2019</td><td style="text-align:right; ">1</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">789</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">$40</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">12/12/2019</td><td style="text-align:right; ">1</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">1011</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">$30</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">14/12/2019</td><td style="text-align:right; ">1</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">789</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">$40</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:right; ">16/10/2019</td><td style="text-align:right; ">1</td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >D2</td><td >{=COUNT(IF($A$2:A2=A2,IF($B$2:B2<>0,IF(B2<>0,IF(MONTH($C$2:C2)=MONTH(C2),$B$2:B2)))))}</td></tr><tr><td >F2</td><td >=SUMPRODUCT((MONTH($C$2:$C$13)=MONTH(E2))*($D$2:$D$13=1)*(B2:B13))</td></tr></table></td></tr></table> <br /><br />

Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself