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]

it is NOT an array formula enter 2018 as quote character followed by 2018 ie '2018

 

[riskier4ra]

the formula is correct it should be in H7 and return 7

I wished I knew why on my development cell a red arrow didnt show up but on my reproduction cells that need to be formatted as text a red arrow shows up. Any thoughts on that?

 

[riskier4ra]

I figured it out. It has to do with storing the number as text...

I choose to ignore it but everytime I use the data validation list to change the date the red flag comes back... Driving me crazy.

 

[riskier4ra]

it is NOT an array formula enter 2018 as quote character followed by 2018 ie '2018

I figured out how to get rid of the error flag. Dumb that excel lets you change a date to text but then tells you its an error... Thank you for all your help.

 

[oldbrewer]

good luck mate (73 yr old limey)

 

[riskier4ra]

good luck mate (73 yr old limey)

Thanks again man I really appreciate the effort and going the extra mile with me (42 yr old rebel with plenty of cause to be one here in Merica) (sic)

 

[riskier4ra]

modify the formula by adding *($b$2:$b$100=Z1)*

Z1 or B1 or whatever is the cell with your selected symbol

Hey there OldBrewer,

I finally got a chance to see how this works and Im a little stumped.


Here is the original "worked" version of the formula you provided. It is on a different sheet.

=SUMPRODUCT((RIGHT(TEXT(SoldIT!$N$10:$N$1001, "yyyy"),4)=TEXT(D2,"yyyy"))*(SoldIT!$H$10:$H$1001))


Column N is the date in long format
D2 is the Helper Cell for the Year
Column H is the Column being Summed


I tried to change it to this with your addition so I could sum the values according to year including a symb criteria

=SUMPRODUCT((RIGHT(TEXT(SoldIT!$N$10:$N$1001, "yyyy"),4)=TEXT(YearGL!D2,"yyyy"))*(SoldIT!$L$10:$L$1001)*(SoldIT!$B$10:$B$1001=SoldIT!M2))


I get NA as a result.


YearGL!D2 is the helper cell

Column L is the Column being Summed
Culumn B is the Symbol Column
M2 is the referenced Symbol


What am I doing wrong? Did I modify the formula in the wrong place?

 

[riskier4ra]

Update: I did fix the extra ) and now I just get Zero, doesnt matter what year I select in the validation list.

It now says =SUMPRODUCT((RIGHT(TEXT(SoldIT!$N$10:$N$1001,"yyyy"),4)=TEXT(YearGL!D2,"yyyy")*(SoldIT!$L$10:$L$1001)*(SoldIT!$B$10:$B$1001=SoldIT!M2)))

Thoughts?

 

[oldbrewer]

TEXT(YearGL!D2,"yyyy")

is that right - your selected year is on sheet YearGL in cell D2

 

[riskier4ra]

TEXT(YearGL!D2,"yyyy")

is that right - your selected year is on sheet YearGL in cell D2

Yes, Column D is the helper column that has formula =("1/1/"&SoldIT!$A$3) and it is formatted as text, and it is referenced to the Data Validation List

I have tried to use SoldIT!$A$3 as well, and it produces the same result. Just a zero.