I am reaching out to you for your help. I am moving a process that was built in Excel into Access and came across with an issue where I am unable to replicate a SUMIFS formula to a work around in Access. I know that there is no such thing as SUMIFS in Access, but there must be a way to get it work. I tried grouping and Crosstab but the issue is that the result that I received was not the result that I was looking for. The grouping did well in scenarios where start date and end date of the event fell into the same months, however, in scenarios where I have a start date for example in 02/01/16 and end date 5/31/16, the result gave me one lump sum that was placed into Sale End Date bucket. The grouping process does not break out the lump sum into per monthly amounts. For Example: Column Y represents Stock Amount, Column X represents Group ID, Column V represents Sale Start Date and column W represents Sale End Date. Since the sale for the Group 1, Stock Amount 32.8 took place for period from 2/1/16 to 5/31/16 I am looking for the following result:
Group ID; Total; February; March; April; May
1; 131.20; 32.8; 32.8; 32.8; 32.8
The formula that I need to replicate is as follows: SUMIFS(MM!$Y:$Y,MM!$X:$X,RIGHT($B4,2),MM! $V:$V,"<="&D$2,MM!$W:$W,">="&D$2).
The logic behind it as follows:
It's basically saying sum the value in column y if the value in column X MM = the value in right(B4,2) MM Stats, AND if the value in column V MM is less than or equal to the value in D2 MM Stats AND if the value in Column W MM is Greater than or equal to the value in D2 MM Stats.
Can someone please help me to resolve this issue? Either formulas or VBA code will work for me.
I am sorry if I missed something...... Is there a problem that there is a similar post is listed on a different Forum/website? What is wrong if the person is trying to get an answer?
the aim is store once, not store multiple times
Remember... Optimize 'til you die!
Progaming environment: Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010 VB based on my own environment: vb6 sp5 ASP based on my own environment: 5.6 VB-NET based on my own environment started 2007 SQL-2005 based on my own environment started 2008 MYLE YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.