Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2016
    Posts
    2

    Unanswered: Moving Sumif formulas into Access

    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 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.

    Thanks.

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Everyone be aware that this is double-posted at

    http://www.access-programmers.co.uk/...d.php?t=287422

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Jun 2016
    Posts
    2

    Moving Sumif formulas into Access

    Hello Missinglinq,
    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?
    Please advise.
    Thank you,

  4. #4
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,399
    Provided Answers: 5
    you dont need sumif in msaccess

    do the suming on the grouping
    hope this help

    See clear as mud


    StePhan McKillen
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •