COUNTIFS in a Date Range

Robyn Keenan

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

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.