Find value according to different criteria and sum

[riskier4ra]

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

 

[oldbrewer]

Heading = Before

31/12/2018

01/01/2020

A1= Date

B1= Sym

C1= Numerical Value

Heading = After

E1= Date

F1= Sym

G1=Numerical Value

results for 2019

date

sym

value

30/11/2018

red

22

red

214

06/12/2018

blue

25

blue

226

12/12/2018

green

28

green

238

18/12/2018

red

31

24/12/2018

blue

34

30/12/2018

green

37

05/01/2019

red

40

11/01/2019

blue

43

17/01/2019

green

46

23/01/2019

red

49

29/01/2019

blue

52

is this what you want

04/02/2019

green

55

10/02/2019

red

58

16/02/2019

blue

61

22/02/2019

green

64

28/02/2019

red

67

06/03/2019

blue

70

12/03/2019

green

73

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

 

[Fluff]

Maybe

Excel 2013/2016
	A	B	C	D	E	F	G	H	I
1	Date	Sym	Value		Date	Sym	Value		Sum
2	01/01/1980	AL1 1HB	96		01/01/1980	AL1 1HB	96		192
3	01/12/2001	AL1 1WB	96		01/12/2000	AL1 1WB	96
4	01/01/1980	AL1 2NF	90		01/01/1980	AL1 2NF	90		180
5	01/01/1980	AL1 4HF	101		01/01/1981	AL1 4HF	101
6	01/01/1980	AL1 5JW	94		01/01/1980	AL1 5JW	94		188
7	01/12/1998	AL10 0PY	78		01/12/1998	AL10 0PY	78		156
8	01/02/2008	AL10 1DJ	84		01/02/2008	AL10 1DJ	84		168
9	01/01/1980	AL10 8LG	102		01/01/1980	AL10 8LG	102		204
10	01/01/1993	AL10 9NE	82		01/01/1993	AL10 9NE	82		164
11	01/01/1980	AL2 1AU	73		01/01/1981	AL2 1AU	73
12	01/02/2009	AL2 2ES	87		01/02/2009	AL2 2ES	87		174
13	01/01/1980	AL2 3PT	78		01/01/1980	AL2 3PT	78		156
14	01/01/1980	AL3 4AT	108		01/01/1980	AL3 4AT	108		216
15	01/12/2001	AL3 5LE	110		01/12/2001	AL3 5LE	110		220
Data

Cell Formulas
Range	Formula
I2	{=IFERROR(SUM(G2,INDEX($C$2:$C$15,MATCH(1,($B$2:$B$15=F2)*(YEAR($A$2:$A$15)=YEAR(E2)),0))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[riskier4ra]

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]

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]

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

 

[riskier4ra]

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]

Did you confirm it with Ctrl Shift Enter?

 

[riskier4ra]

Did you confirm it with Ctrl Shift Enter?

No, I didnt. That worked though! Thanks! Out of curiosity why do I need to do CSE in some cases and not in others?

 

[Fluff]

Most array formulae need to be confirmed with Ctrl Shift Enter.
Have a look here https://www.ablebits.com/office-addins-blog/2015/02/25/array-formulas-functions-excel/