Find value according to different criteria and sum

riskier4ra

Board Regular
Joined
Dec 5, 2017
Messages
101
Hey there, anyone know how I can do the following? My guess is I would use Index Match but not sure how to involve the date aspect.

I need to sum some values that match by a common symbol under two different headings, but only if they fall within the same calendar year.

So. If sym A matches sym B check date for year and if the same sum the value of C1 & G1. If year in After does not match the year found in Before do nothing. Do not want to use SUMIF, would prefer to use SUMPRODUCT

Heading = Before
A1= Date
B1= Sym
C1= Numerical Value

Heading = After
E1= Date
F1= Sym
G1=Numerical Value

Thanks for any help you all can give,
Risk
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
11,003
Heading = Before31/12/201801/01/2020
A1= Date
B1= Sym
C1= Numerical Value
Heading = After
E1= Date
F1= Sym
G1=Numerical Value
results for 2019
datesymvalue
30/11/2018red22red214
06/12/2018blue25blue226
12/12/2018green28green238
18/12/2018red31
24/12/2018blue34
30/12/2018green37
05/01/2019red40
11/01/2019blue43
17/01/2019green46
23/01/2019red49
29/01/2019blue52is this what you want
04/02/2019green55
10/02/2019red58
16/02/2019blue61
22/02/2019green64
28/02/2019red67
06/03/2019blue70
12/03/2019green73

<colgroup><col><col span="8"><col span="2"><col span="2"></colgroup><tbody>
</tbody>
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,010
Office Version
  1. 365
Platform
  1. Windows
Maybe

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Date</td><td style=";">Sym</td><td style=";">Value</td><td style="text-align: right;;"></td><td style=";">Date</td><td style=";">Sym</td><td style=";">Value</td><td style="text-align: right;;"></td><td style=";">Sum</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">01/01/1980</td><td style=";">AL1 1HB</td><td style="text-align: right;;">96</td><td style="text-align: right;;"></td><td style="text-align: right;;">01/01/1980</td><td style=";">AL1 1HB</td><td style="text-align: right;;">96</td><td style="text-align: right;;"></td><td style="text-align: right;;">192</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">01/12/2001</td><td style=";">AL1 1WB</td><td style="text-align: right;;">96</td><td style="text-align: right;;"></td><td style="text-align: right;;">01/12/2000</td><td style=";">AL1 1WB</td><td style="text-align: right;;">96</td><td style="text-align: right;;"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">01/01/1980</td><td style=";">AL1 2NF</td><td style="text-align: right;;">90</td><td style="text-align: right;;"></td><td style="text-align: right;;">01/01/1980</td><td style=";">AL1 2NF</td><td style="text-align: right;;">90</td><td style="text-align: right;;"></td><td style="text-align: right;;">180</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">01/01/1980</td><td style=";">AL1 4HF</td><td style="text-align: right;;">101</td><td style="text-align: right;;"></td><td style="text-align: right;;">01/01/1981</td><td style=";">AL1 4HF</td><td style="text-align: right;;">101</td><td style="text-align: right;;"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">01/01/1980</td><td style=";">AL1 5JW</td><td style="text-align: right;;">94</td><td style="text-align: right;;"></td><td style="text-align: right;;">01/01/1980</td><td style=";">AL1 5JW</td><td style="text-align: right;;">94</td><td style="text-align: right;;"></td><td style="text-align: right;;">188</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">01/12/1998</td><td style=";">AL10 0PY</td><td style="text-align: right;;">78</td><td style="text-align: right;;"></td><td style="text-align: right;;">01/12/1998</td><td style=";">AL10 0PY</td><td style="text-align: right;;">78</td><td style="text-align: right;;"></td><td style="text-align: right;;">156</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">01/02/2008</td><td style=";">AL10 1DJ</td><td style="text-align: right;;">84</td><td style="text-align: right;;"></td><td style="text-align: right;;">01/02/2008</td><td style=";">AL10 1DJ</td><td style="text-align: right;;">84</td><td style="text-align: right;;"></td><td style="text-align: right;;">168</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">01/01/1980</td><td style=";">AL10 8LG</td><td style="text-align: right;;">102</td><td style="text-align: right;;"></td><td style="text-align: right;;">01/01/1980</td><td style=";">AL10 8LG</td><td style="text-align: right;;">102</td><td style="text-align: right;;"></td><td style="text-align: right;;">204</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;">01/01/1993</td><td style=";">AL10 9NE</td><td style="text-align: right;;">82</td><td style="text-align: right;;"></td><td style="text-align: right;;">01/01/1993</td><td style=";">AL10 9NE</td><td style="text-align: right;;">82</td><td style="text-align: right;;"></td><td style="text-align: right;;">164</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;">01/01/1980</td><td style=";">AL2 1AU</td><td style="text-align: right;;">73</td><td style="text-align: right;;"></td><td style="text-align: right;;">01/01/1981</td><td style=";">AL2 1AU</td><td style="text-align: right;;">73</td><td style="text-align: right;;"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;">01/02/2009</td><td style=";">AL2 2ES</td><td style="text-align: right;;">87</td><td style="text-align: right;;"></td><td style="text-align: right;;">01/02/2009</td><td style=";">AL2 2ES</td><td style="text-align: right;;">87</td><td style="text-align: right;;"></td><td style="text-align: right;;">174</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;;">01/01/1980</td><td style=";">AL2 3PT</td><td style="text-align: right;;">78</td><td style="text-align: right;;"></td><td style="text-align: right;;">01/01/1980</td><td style=";">AL2 3PT</td><td style="text-align: right;;">78</td><td style="text-align: right;;"></td><td style="text-align: right;;">156</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="text-align: right;;">01/01/1980</td><td style=";">AL3 4AT</td><td style="text-align: right;;">108</td><td style="text-align: right;;"></td><td style="text-align: right;;">01/01/1980</td><td style=";">AL3 4AT</td><td style="text-align: right;;">108</td><td style="text-align: right;;"></td><td style="text-align: right;;">216</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="text-align: right;;">01/12/2001</td><td style=";">AL3 5LE</td><td style="text-align: right;;">110</td><td style="text-align: right;;"></td><td style="text-align: right;;">01/12/2001</td><td style=";">AL3 5LE</td><td style="text-align: right;;">110</td><td style="text-align: right;;"></td><td style="text-align: right;;">220</td></tr></tbody></table><p style="width:3.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Data</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I2</th><td style="text-align:left">{=IFERROR(<font color="Blue">SUM(<font color="Red">G2,INDEX(<font color="Green">$C$2:$C$15,MATCH(<font color="Purple">1,(<font color="Teal">$B$2:$B$15=F2</font>)*(<font color="Teal">YEAR(<font color="#FF00FF">$A$2:$A$15</font>)=YEAR(<font color="#FF00FF">E2</font>)</font>),0</font>)</font>)</font>),""</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 

riskier4ra

Board Regular
Joined
Dec 5, 2017
Messages
101
Yes. I need to find all data within a given year and sum it for both headings independently. I also need to find all data within a given month and do the same thing. Just thought I would tackle one at a time.
 

riskier4ra

Board Regular
Joined
Dec 5, 2017
Messages
101

ADVERTISEMENT

If I wanted to use that formula to do the same thing but look for data that matches a month name what would I change? I tried to change the YEARS value to MONTH but that didnt work. Do I need to change the 1 after match to the month name?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,010
Office Version
  1. 365
Platform
  1. Windows
If you only want to sum the values if Sym & month are the same, then use
=IFERROR(SUM(G2,INDEX($C$2:$C$15,MATCH(1,($B$2:$B$15=F2)*(MONTH($A$2:$A$15)=MONTH(E2)),0))),"")
Confirmed with CSE
 
Last edited:

riskier4ra

Board Regular
Joined
Dec 5, 2017
Messages
101

ADVERTISEMENT

If you only want to sum the values if Sym & month are the same, then use
=IFERROR(SUM(G2,INDEX($C$2:$C$15,MATCH(1,($B$2:$B$15=F2)*(MONTH($A$2:$A$15)=MONTH(E2)),0))),"")
Confirmed with CSE

The sum cell goes blank when I change YEAR to MONTH even though the month and sym are the same
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,010
Office Version
  1. 365
Platform
  1. Windows
Did you confirm it with Ctrl Shift Enter?
 

Watch MrExcel Video

Forum statistics

Threads
1,108,509
Messages
5,523,312
Members
409,511
Latest member
hitesh222002

This Week's Hot Topics

Top