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

 

[Aladin Akyurek]

Just wanted to say thanks for the help you did give. Im sure it will prove useful someday. Cheers mate.

You should really have provided the sample of the picture in such a way that it can be copied into Excel.

 

[oldbrewer]

2018							cell A1 has 2018 entered as text			01/01/2018
	01/04/2017	4					cell M1 converts this to 1/1/18			31/12/2018
	07/06/2017	7					cell M2 converts this to 31/12/18
	13/08/2017		-4
	19/10/2017		-5
	25/12/2017	9						sum of pos	sum of neg
	02/03/2018	3						23	-15
	08/05/2018	8
	14/07/2018		-6
	19/09/2018	12
	25/11/2018		-9
	31/01/2019		-55
	08/04/2019	44
								formula giving 23
								=SUMPRODUCT(($B$2:$B$13>=$M$1)*($B$2:$B$13<=$M$2)*($C$2:$C$13))

<colgroup><col><col><col span="6"><col><col><col span="2"><col><col span="3"></colgroup><tbody>
</tbody>

 

[riskier4ra]

2018							cell A1 has 2018 entered as text			01/01/2018
	01/04/2017	4					cell M1 converts this to 1/1/18			31/12/2018
	07/06/2017	7					cell M2 converts this to 31/12/18
	13/08/2017		-4
	19/10/2017		-5
	25/12/2017	9						sum of pos	sum of neg
	02/03/2018	3						23	-15
	08/05/2018	8
	14/07/2018		-6
	19/09/2018	12
	25/11/2018		-9
	31/01/2019		-55
	08/04/2019	44
								formula giving 23
								=SUMPRODUCT(($B$2:$B$13>=$M$1)*($B$2:$B$13<=$M$2)*($C$2:$C$13))

<tbody>
</tbody>

Hi OldBrewer, Is it possible to use with index match so dates dont have to placed in M1&M2, and I can just change the value in A1 dynamically without having to build a date table? I guess what Im trying to do is sort of like what vlookup does but sum the values associated with the dates that it finds. This is definitely closer to what I'm looking for. Really appreciate the help!

 

[riskier4ra]

You should really have provided the sample of the picture in such a way that it can be copied into Excel.

I'll keep that in mind for future reference. Thanks for the help.

 

[riskier4ra]

Also, the values are not coming out.

 

[riskier4ra]

Can I see the formula's for this one too?

 

[oldbrewer]

01/01/2018

01/03/2017

3

04/06/2017

-9

07/09/2017

5

sum of pos

sum of neg

11/12/2017

-8

55

-13

16/03/2018

22

19/06/2018

-7

22/09/2018

33

26/12/2018

-6

31/03/2019

44

A1 is entered as a nomal date

55 from

=SUMPRODUCT((RIGHT(TEXT($B$2:$B$10,"yyyy"),4)=TEXT($A$1,"yyyy"))*($C$2:$C$10))

-13 from

=SUMPRODUCT((RIGHT(TEXT($B$2:$B$10,"yyyy"),4)=TEXT($A$1,"yyyy"))*($D$2:$D$10))

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

 

[riskier4ra]

01/01/2018

01/03/2017

3

04/06/2017

-9

07/09/2017

5

sum of pos

sum of neg

11/12/2017

-8

55

-13

16/03/2018

22

19/06/2018

-7

22/09/2018

33

26/12/2018

-6

31/03/2019

44

A1 is entered as a nomal date

55 from

=SUMPRODUCT((RIGHT(TEXT($B$2:$B$10,"yyyy"),4)=TEXT($A$1,"yyyy"))*($C$2:$C$10))

-13 from

=SUMPRODUCT((RIGHT(TEXT($B$2:$B$10,"yyyy"),4)=TEXT($A$1,"yyyy"))*($D$2:$D$10))

<tbody>
</tbody>

That's awesome. Thank you! Any chance A1 can just be the year i.e 2018? I tried to change the format of it to yyyy but that didnt work.

 

[oldbrewer]

format A1 as yyyy - it will still be 1/1/2018 but display as 2018

otherwise use a hidden helper cell, maybe ?

 

[riskier4ra]

format A1 as yyyy - it will still be 1/1/2018 but display as 2018

otherwise use a hidden helper cell, maybe ?

Im using a data validation list to select a year.

Could I see the code difference for your earlier suggestion that included symbol?