SUM Function to count values in multiple rows in a date range-CodePudding

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

CodePudding user response：

use:

=SUMIFS(C2:C, A2:A, ">="&E3, B2:B, "<"&F3)

CodePudding user response：

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)