COUNTIFS in a Date Range

I'm attempting to count how many times a sales rep's name appears in a column and the deal they're working on will be closed within a date range. I'm using a countifs statement with an AND for the criteria on the second range. I receive an INVALID OPERATION result when I use AND, but if I only look for either greater than or less than a date, the formula works. Please help!

=COUNTIFS([Sales Rep]:[Sales Rep], [Column50]@row, [Close Date]:[Close Date], AND(>=[Column53]1, <=[Column54]1))

[Column50]@row is the employee's name

[Column53]1 is the beginning of the date range

[Column54]1 is the end of the date range

Find more posts tagged with

Formulas

Accepted answers

All comments

J Tech

Hi Robyn,

You shouldn't need the AND statement, see below formula that should work.

Are you intending this for sheet summary with a set range or to be used on a data sheet?

=COUNTIFS([Column50]:[Column50], [Sales Rep]@row, [Column53]:[Column53], >=[Start Date]@row, [Column54]:[Column54], <=[Close Date]@row)

Robyn Keenan

Thanks J Tech! It solved the invalid operation error, but it's not returning an accurate count. I'm building a table of the counts to the right of the dataset so Column 50, 53, & 54 are all just reference points to the list of names and dates in the smartsheet table.

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.