how to write formulas to get the answer of "?" in pic below.

Michael Giovanni · 09/14/20

The date are in string (manually typed)

I want to count how many different date in a month

for example 22-jan-20 and 21-jan-20 are counted as 2

Please help me for the smartsheet formula

Genevieve P. · 09/14/20

Thank you for clarifying! Yes, in that case we would need to add in a DISTINCT function as well, to only count the unique times that this string appears while containing "Jan":

=COUNTIF(DISTINCT(Dates:Dates), CONTAINS("Jan", @cell))

Does this now meet all of your criteria?

Genevieve P. · 09/14/20

Hi @Michael Giovanni

You can use a combination of a COUNTIF formula and a MONTH function (using IFERROR to get rid of any blank cells).

Try this for your January count:

=COUNTIF(Dates:Dates, IFERROR(MONTH(@cell), 0) = 1)

Then to change what month you're looking for, just change the 1 at the very end of the formula to be that month's number. For example, if I was looking for April, I'd use this:

=COUNTIF(Dates:Dates, IFERROR(MONTH(@cell), 0) = 4)

Let me know if this works for you!

Cheers,

Genevieve

Genevieve P. · 09/14/20

My apologies Michael, I just read that your dates are in a string, not in a Date Type of column. The formula above is for a Date Type of column.

In this instance, you would need to use CONTAINS to check and see if your dates column contains a certain word. Please note that I've titled my column "Dates" for the range, so you will need to swap this out for your current date column name:

=COUNTIF(Dates:Dates, CONTAINS("Jan", @cell))

Then for each Month, you would want to change the value "in these" that you are searching for:

=COUNTIF(Dates:Dates, CONTAINS("Apr", @cell))

Cheers,

Genevieve

Michael Giovanni · 09/14/20

https://community.smartsheet.com/discussion/comment/262699#Comment_262699

Thank you for responding

The formula doesn't work. But even if it is working, I don't think that I will get the answer that I want.

If you look at the example pic, there are 3 "January" but only 2 with different date.

I don't want to count the same date.

Michael Giovanni · 09/14/20

https://community.smartsheet.com/discussion/comment/262700#Comment_262700

This one works, but it will count month with the same date.

The problem is I don't want to count multiple date

Genevieve P. · 09/14/20

Hi @Michael Giovanni

Thank you for clarifying! Yes, in that case we would need to add in a DISTINCT function as well, to only count the unique times that this string appears while containing "Jan":

=COUNTIF(DISTINCT(Dates:Dates), CONTAINS("Jan", @cell))

Does this now meet all of your criteria?

Michael Giovanni · 09/14/20

https://community.smartsheet.com/discussion/comment/262703#Comment_262703

Marvelous, this works well. I know that it will work with distinct, but apparently I'm not smart enough to write it in this order.

Thank you very much!!! Really appreciate your help.

🍪 here's a cookie for you.

Genevieve P. · 09/14/20

Haha no problem at all! Thank you for the cookie 😋

how to write formulas to get the answer of "?" in pic below.

Best Answer

Answers

Help Article Resources

Categories