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]

delete the validation list and enter a year manually ( for test purposes )

 

[riskier4ra]

delete the validation list and enter a year manually ( for test purposes )

Did that. All the other stuff still works, but that cell just produces zero. The symb in M2 is the same as the symb entered in column B. M2 is Regular case, and in B:B its all upper case. I have tried to change that as well to see if that would make a difference. But still zero.

 

[oldbrewer]

try entering the year as '2018 as well as a date

 

[riskier4ra]

try entering the year as '2018 as well as a date

I tried using 'before the year. It didnt return the result in M3 where the formula is being used and it didnt break anything else.

I tried using a long form date and it didnt produce anyting in M3 and it broke all the other things we did.

I have an Idea. Im pretty good at reverse engineering what other people do and make it work for myself the way I need it too.

Since you never actually created a working example with the sym criteria, would it be possible that you would and I can see it actually work?

 

[riskier4ra]

try entering the year as '2018 as well as a date

I have another SUMPRODUCT formula I use another way that allows me to search and sum on a sym but I have no Idea how I would incorporate it doing it based on a year.


I use =SUMPRODUCT(--('[file]sheetname'!range=symcelllocation),'[file]sheetname'!range)

So it basically looks down the range and finds all occurrences of a symbol and only sums the values using that symbol. This is what I use in the current setup that uses a basic calendaring system that I am trying to get away from. It works but is has many limitations which cause defects I want to cure.

 

[oldbrewer]

what are the symbols ? I still don't know what you are actually doing ?

 

[riskier4ra]

what are the symbols ? I still don't know what you are actually doing ?

Trying to collect values according to different needs and present it in an organized way that makes sense to me.

When I say symbols they arent anything more than text. For example the recent sumproduct formula I showed you that I use on another part of my project looks for the month name so it cannot pull the wrong data into a data table that I developed. This new need has thrown me a little bit of a curve ball but also an opportunity to turn the entire system into a single sheet ledger style system instead of a 12 month sheet that limits my data entry, and this is where your wisdom is coming to into play.

We found that if we ran out of space in a month we would have to start combining entries and that undermines part of my long term goals.

Its not something that I can explain simply, I have been working on this project for about 4 years with about 30 months of actual nonstop work in it. Over the winter I got into something new and thought it would be a perfect opportunity to develop it into my project so this is where we are.

The symbol part is not needed but would be extremely useful, and then there is one last goal to get all the fields I am pulling data into with these formulas to look at the whole picture and tell me what it is, instead of just by year or by month. If you have contact information you want to share, since you have helped me with a part of this, when its complete I will be happy to send you a copy for your review. Who knows, you may even be able to help take it to the next level. It is about 99% done, at least this part of it. It has 3 big parts, with the first being 99% complete. The ledger system will take it to the next level and later I intend to convert it to a form type of system, and would actually, if I ever have the money to pay someone, like to turn it into a php/mysql website. I know back in the day, I think office 98, we use to be able to save these worksheets as html docs we could actually use but they took that away and now you have to pay people to do it... I got into the computer scene around 3.1 right before windows 95 came out, and then the internet. Actually had the ability to act on bulletin board systems. Something an old brewer like yourself could appreciate, im sure. I ended up later getting my MCSE+I on the original NT track. Just sharing :0)

 

[oldbrewer]

I will post a "example" spreadsheet in a few minutes and you can clarify further - this forum does not allow off forum stuff - sorry.

 

[riskier4ra]

I will post a "example" spreadsheet in a few minutes and you can clarify further - this forum does not allow off forum stuff - sorry.

Yeah I noticed I couldnt private message you. Kind of strange but its their show. I appreciate having a place to get assistance on this. Does it allow links to sources like say a video where I can show you what Im talking about that someone else has done?

 

[oldbrewer]

date	purchases	sales							purchases	sales
09/10/2018	47	68						01/10/2018	867	1256
20/10/2018	484	701						01/11/2018	556	806
31/10/2018	336	487						01/12/2018	987	1430
11/11/2018	118	171						01/01/2019	1108	1605
22/11/2018	438	635						01/02/2019	391	566
03/12/2018	496	719						01/03/2019	898	1300
14/12/2018	304	440						01/04/2019	564	816
25/12/2018	187	271						01/05/2019	450	651
05/01/2019	257	372						01/06/2019	851	1233
16/01/2019	490	710						01/07/2019	829	1201
27/01/2019	361	523						01/08/2019	894	1295
07/02/2019	274	397						01/09/2019	590	855
18/02/2019	117	169						01/10/2019	0	0
01/03/2019	442	640						01/11/2019	0	0
12/03/2019	92	133						01/12/2019	0	0
23/03/2019	364	527
03/04/2019	176	255							8985	13014
14/04/2019	131	189
25/04/2019	257	372								21999
06/05/2019	6	8
17/05/2019	296	429
28/05/2019	148	214
08/06/2019	240	348
19/06/2019	492	713
30/06/2019	119	172
11/07/2019	464	672
22/07/2019	365	529
02/08/2019	119	172					you can add departments, stock types, anything
13/08/2019	437	633
24/08/2019	338	490					and make multiple dahboard tables
04/09/2019	252	365
15/09/2019	338	490					or have a query sheet
	8985	13014					where you enter the month, stock type etc

<colgroup><col><col span="7"><col><col span="6"></colgroup><tbody>
</tbody>