How to COUNTIFS with DISTINCT dates and other range/criteria?

leejohnny

Hi,

I am trying to write a COUNTIFS formula, taking into consideration of X criteria for a range of cells in addition to DISTINCT dates within Y range. Can anyone share any insight how to write this in a formula statement? I'll provide an example below.

I have a list entries in Smartsheet. They have dates associated with each entry. I want to count the # of entries that are indicated as "F2F (Face to Face)" interactions with customers. I also want to count it by month; therefore, want to count in a way that indicates "between October 1st 2023 to October 31st 2023" as an example. Some of these entries will have multiple F2F interactions on the same day (eg. Oct 10 2023) but I want to count these entries distinctively/uniquely - so those on the same day are not counted twice. How do I do this?

"Date" column has dates populated.

"Format" column has F2F indicated.

Much appreciated. Thank you!

KPH

Can you explain how an entry can have multiple F2F entries on the same day? Is it one row with multiple things in the F2F column and one date in the date column?

If you can share a (redacted if necessary) screenshot or mock up of the data that would be helpful.

leejohnny

Hi KPH,

As a background, I set up a form with the Smartsheet so each entry = one row in the Smartsheet. Those submitting the form can submit as much as they like and can indicate the same date, if applicable.

I've attached a screenshot/mock up version below.

So I want to count the number of "entries" where the format is listed as F2F but I do not want to double count on those that appear on the same date (dates are distinct). So according to the screenshot above, I want the formula to calculate a total of 4 because there are 4 F2F entries with distinct dates (Oct 1, 2, 10, and 20). I do not want to double count the Oct 1 and Oct 10.

Does this make sense? Apologies if it is confusing!!

Paul Newcome

You would use a COUNT/DISTINCT/COLLECT combo like so:

=COUNT(DISTINCT(COLLECT(Date:Date, Format:Format, @cell = "F2F")))

leejohnny

Hi Paul, thanks for your comment. How would I add in the date ranges to the formula as well? I want to add in ranges like Oct 1-31, Nov 1-30, and Dec 1-31 so I can calculate the distinct dates that have a F2F format on a monthly basis and also quarterly basis. Would appreciate your expertise. Thanks!

KPH

Hi

I don't think the COUNT/DISTINCT/COLLECT combo needs the @cell= and have used this formula instead

=COUNT(DISTINCT(COLLECT(Date:Date, Format:Format, "F2F")))

A simple way to add a date range would be to include the month and year in a helper column in your sheet that you can hide.

This column formula would create a column with both the F2F and the year and month in one cell

=IF(Format@row = "F2F", Format@row + "-" + YEAR(Date@row) + "-" + MONTH(Date@row))

You can then adjust your COUNT to look at this column instead of the Format column

=COUNT(DISTINCT(COLLECT(Date:Date, Helper:Helper, "F2F-2023-10")))

It will still count the distinct dates but only where the Helper column is F2F-2023-10

KPH

If you want to use this formula in a table to create something like this

You do not need to edit this part of the formula each time

=COUNT(DISTINCT(COLLECT(Date:Date, Helper:Helper, "F2F-2023-10")))

Instead, you can use the values in the year and month columns like this:

=COUNT(DISTINCT(COLLECT(Date:Date, Helper:Helper, "F2F-" + [Column4]@row + "-" + [Column5]@row)))

Or for easier sorting a table like this

=COUNT(DISTINCT(COLLECT(Date:Date, Helper:Helper, "F2F-" + YEAR([col11]@row) + "-" + MONTH([col11]@row))))

Paul Newcome

@KPH While the @cell reference may not be needed this time, I have come across instances with this combo and dates as well as other functions with dates where it didn't work as expected unless you use @cell references. When used properly, they don't hurt anything being in there, but when not used it can sometimes break. I have just gotten into the habit of always using @cell references just in case.

@leejohnny To narrow it down to within certain date ranges, you do not need any helper columns. You can just evaluate the date column directly like so:

=COUNT(DISTINCT(COLLECT(Date:Date, Date:Date, AND(@cell>= DATE(2023, 10, 01), @cell<= DATE(2023, 10, 31)), Format:Format, @cell = "F2F")))

In the above case, the @cell reference is definitely needed at least within the AND function.