Moving Sumif formulas into Access (1 Viewer)

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.

you need a totals query.

design a query that selects the item you want.

click the sigma button to turn it into a totals query. then you can select whatever domain formula you require.


I am not very good at writing SQL aircode off the bat, but it will resemble this sort of thing.

select totals as sumof(fieldname) where someotherfield = somevalue

ie. show a total of a particular field for items satisfying a given condition - ie, your sumif

to get monthly totals you need a groupby clause. If you just use he date, you get a separate total for each date. so you need a way of grouping dates together into the months. you probably need to include the year as well, for standard practice, otherwise as time goes on, you will get data for different years adding together.

select totals groupby month(itemdate)

so in a visual query you need something that will change your date into a expression representing a month.

try format(itemdate,"YYYY-MM").
Then make this a group by in your totals query.

Everyone be aware that this is double-posted at

http://www.dbforums.com/showthread.php?1709341-Moving-Sumif-formulas-into-Access

Linq ;0)>

If you want to double post, then give us the courtesy of knowing. Otherwise people here could be wasting their time giving help when your query has been answered elsewhere.

More here.

Hello Cronk,
I understand your concern. I am still searching for the right answer. I am currently trying out Dave's suggestion. So far did not work for me.
Thank you,

Hello Everyone, Thank you for helping with the above listed question. I found my solution in VBA code. This issue is closed.
Thanks again.