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

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.

And

[Starting Date]2

is the cell - where the starting date is

and

[End Date]2

is the cell - where the end date is

THANKS FOR YOU HELP!!

Find more posts tagged with

Accepted answers

All comments

Debbie Sawyer

Hello

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

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.

Haley C.

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

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

DATE(yyyy,mm,dd)

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.