How to convert a SUMIFS function in Excel into MS access function RRS feed

  • Question

  • Basically I am trying to replicate what is there in Excel into access.

    In Excel I have function for one column


    How can I achieve it in access

    Tuesday, September 4, 2018 8:03 PM

All replies

  • Hi,

    Sorry, I'm not good with Excel. What does this formula do?

    Even when I look up the syntax for SUMIF here, I'm not sure I understand what yours is doing.

    Can you please explain what you need the Access formula to do using plain words? Thanks.

    Tuesday, September 4, 2018 8:15 PM
  • Let's take the example in the online help for the SUMIFS function:

    'For example, you would use SUMIFS to sum the number of retailers in the country who (1) reside in a single zip code and (2) whose profits exceed a specific dollar value.'   

    In a relational database this would typically be done by a query which draws upon a subquery which uses the WHERE clause to restrict the query on a ZipCode column, and a HAVING clause which restricts the subquery's result table to those retailers whose Sales less Expenses are greater than a specific amount, e.g.

    SELECT SUM(RetailerProfits) AS TotalProfits
        (SELECT Retailer, SUM(Sales - Expenses) AS RetailerProfits
         FROM Retailers INNER JOIN Accounts
         ON Retailers.RetailerID = Accounts.RetailerID
         WHERE ZipCode = "98765"
         AND AccountDate BETWEEN #2017-04-01# AND #2018-03-31#
         GROUP BY Retailer
         HAVING SUM(Sales - Expenses) > 100000);

    This would return the total profits by those retailers in zip code 98765 who have each made a profit greater than 100,000 USD in the accounting year beginning 1 April 2017.

    Ken Sheridan, Stafford, England

    Tuesday, September 4, 2018 9:47 PM