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

 

[riskier4ra]

Is there a way to make the sum formula in I2 sum the entire range instead of having to sum for the same values on I3 I4 I5 etc?

 

[Fluff]

Possibly, but if so it's beyond knowledge.

 

[Aladin Akyurek]

Is there a way to make the sum formula in I2 sum the entire range instead of having to sum for the same values on I3 I4 I5 etc?

Try to post a sample along with the results which must obtain.

 

[riskier4ra]

 

[Aladin Akyurek]

Care to post this in a way that it can be copied in Excel cells, not as an image/picture which requires retyping what it shows.

 

[riskier4ra]

Id love to but for some reason the icon to insert a table is not on my toolbar

 

[Aladin Akyurek]

Id love to but for some reason the icon to insert a table is not on my toolbar

Have a look at: https://www.mrexcel.com/forum/about-board/508133-attachments-post2507729.html#post2507729

 

[riskier4ra]

Have a look at: https://www.mrexcel.com/forum/about-board/508133-attachments-post2507729.html#post2507729

Hmm. Its there now. Strange.

Lets look at it from another angle.

Lets say in cell A1 I have a year. 2019 for example.

Lets say in B:B I have dates that range from 2018 to 2019

and in C:C I have negative values
and in D:D I have positive values

If A1 is 2018 I want to find all dates in B:B that have the 2018 in them and then sum the associated values in column C and do the same thing for the values in column D.

I am using a calendar method right now to input the values in each month and am using SUMPRODUCT in a 12 month data table to pull these values into, in two different rows, so I can output them on a document.

What I want to do is get away from the Calendar method and just use a basic ledger style sheet where all the entries are line by line and I can pull the values for each year by changing the date in A1.

Does this make more sense? What I am working on is kind of sensitive so I am trying to find an answer to my problem in a way that I can reproduce the result to fit my needs without giving out too many details.

 

[riskier4ra]

Hmm. Its there now. Strange.

Lets look at it from another angle.

Lets say in cell A1 I have a year. 2019 for example.

Lets say in B:B I have dates that range from 2018 to 2019

and in C:C I have negative values
and in D:D I have positive values

If A1 is 2018 I want to find all dates in B:B that have the 2018 in them and then sum the associated values in column C and do the same thing for the values in column D.

I am using a calendar method right now to input the values in each month and am using SUMPRODUCT in a 12 month data table to pull these values into, in two different rows, so I can output them on a document.

What I want to do is get away from the Calendar method and just use a basic ledger style sheet where all the entries are line by line and I can pull the values for each year by changing the date in A1.

Does this make more sense? What I am working on is kind of sensitive so I am trying to find an answer to my problem in a way that I can reproduce the result to fit my needs without giving out too many details.

I guess I will start another thread. Thanks anyways.

 

[riskier4ra]

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