Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – SUM Function to count values in multiple rows in a date range

Hoping to get some help on a rather interesting formula!

  • Column A = Start Date
  • Column B = End Date
  • Column C = # Sales

Goal: create a calculator on the same sheet that allows me to input two dates (Start Date/End Date), and have the output be the sum of all values in Column C within that defined range (see image).

Problem: figuring out a way to have the formula recognize and sum the data in between the rows that are within the data range but not directly equal to the values that define the range (in other words, I can only figure out formulas that sum the first and last rows of the array)

Thanks in advance!! Here’s the link to a sample spreadsheet with sample data: https://docs.google.com/spreadsheets/d/1d3fKGXdvVPFJNJA-TgHbH7t250GS2pQQCWvQC31T5Ow/edit#gid=0

Screenshot of spreadsheet

Tags:

3

Answers


  1. 0

    use:

    =SUMIFS(C2:C, A2:A, ">="&E3, B2:B, "<"&F3)
    Login or Signup to reply.
  2. 0

    Another option if you want to write SQL:

    =QUERY(
    A:C,
    "
    SELECT 
      SUM(C) 
    WHERE 
      A >= DATE '"&TEXT(E3, "yyyy-mm-dd")&"' 
      AND B < DATE '"&TEXT(F3, "yyyy-mm-dd")&"' 
    LABEL 
      SUM(C) ''
    "
    , 1)
    Login or Signup to reply.
  3. 0

    You can also try using FILTER in combination with sum.

    I find this approach nicer since the SUMIFS has some strange shorthand.

    =SUM(FILTER(C2:C14,A2:A14>=E3,B2:B14<F3))

    If you use filter you might have an extra column saying the city (e.g. column D would be Dallas or Copenhagen), and then you could filter that also. In your case you would add to the filter, D2:D14="Dallas".

    I put the code in your shared sheet.

    Cheers Mads

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search