How to count number of cells between two dates from another sheet

Cyril ✭✭
edited 12/09/19 in Smartsheet Basics

 Hi there, 

I have created a Sales Activity Tracker - where I want to show the number of new leads we had between two specific dates. 

So in the sales activity tracker - I will have two cells - one with the starting date and one with the end date.

And in my master sheet - I have a column named "Date Leads In" with the date for each lead. 

I tried to use the COUNTIF formula but so far it's not working:  

=COUNTIF({Flat file - final Range 1},=<[Starting Date]2, {Flat file - final Range 1}=>[End Date]2)

Knowing that...

Flat file - final Range 1

is the name it gave me when I link to another sheet named

Flate file - final

and I've selected the column that has the dates when we got a new lead. 


[Starting Date]2

is the cell - where the starting date is


[End Date]2

is the cell - where the end date is




  • Debbie Sawyer
    Debbie Sawyer ✭✭✭✭✭✭


    Are you getting an error message?

    I would try editing your formula to match this...

    =COUNTIF({Flat file - final Range 1},<=[Starting Date]2, {Flat file - final Range 1},>=[End Date]2)

    I just swapped the =< to <= and => to >= and I added a comma between your second range and second condition statement.

    Does this work?

    Kind regards

    Debbie Sawyer Consultant & Training Manager


    Smarter Processes mean happy people in successful businesses

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    edited 03/07/19

    The pprimary issue is that you are using multiple sets of range/criteria, but only using a COUNTIF instead of a COUNTIFS. That S on the end allows you to use multiple sets of range/criteria. You can also use COUNTIFS with a singe range, so I personally have gotten into the habit of always using the S (same with SUMIF vs SUMIFS).


    Also... Since you have multiple conditions for the same range, you can use the AND function to save yourself a little time.


    =COUNTIFS({Flat file - final Range 1}, AND(@cell >= [Starting Date]2, @cell <= [End Date]2))


    Give this a try and see if it works for you.


    PS: I swapped your < and > signs to reflect greater than the start date and less than the end date. The way they were set in your original formula, you would have counted all leads EXCEPT for the ones that fell between the start and end dates.

  • Hey @Paul Newcome, stumbled across this formula and it seems to fit my purpose, however I keep getting an #UNPARSEABLE error. Here's what I'm working with:

    =COUNTIFS({External Sheet - Range 5}, AND(@cell >= [12/1/22], @cell <= [12/31/22]))

    I'm sure there's a fairly obvious problem that I'm just not seeing. You responded to this was quite a while ago so I'm not really expecting a response, but on the off chance you see this, would love any help you might be able to offer. Thanks!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Haley C. When using dates in a formula, you must use the DATE function.


    =COUNTIFS({External Sheet - Range 5}, AND(@cell >= DATE(yyyy,mm,dd), @cell <= DATE(yyyy,mm,dd)))