Hello Everyone,
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 followsSUMIFS(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.
Thanks.
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 followsSUMIFS(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.
Thanks.