Counting and Dates

Sarah Zimmerman · 08/27/20

I've created a complicated cross-sheet formula that is supposed to count any number of inquiries I have in a specific month. So, basically, the formula is supposed to count anything in Range 5 (named "Inquiry Date") and calculates the date, entering the total on a separate sheet. So if I have 4/3/20 in any cell in Range 5, it will count it in April so that I can look and see that we had 5 or 30 or however many inquiries in the month of April. I hope that makes sense. I am trying to pull all of this information on a dashboard.

I'm not even sure how to begin to approach this but this is what I tried:

=COUNTIFS([Inquiry Date], @cell <> "", = APRIL(TODAY(30)))

It returns as unparseable.

I've also tried:

=COUNTIFS({Media Tracking Range 5}, @cell > DATE(2018, 12, 1), {Media Tracking Range 5}, @cell < DATE(2018, 12, 31))

This isn't unparseable but it doesn't count anything. All it shows is 0.

SoS | Dan Palenchar · 08/27/20

Your first one is not parsable because their is no range specified in your search range. If you want to search the whole of the Inquiry Date column use [Inquiry Date]:[Inquiry Date]

Something like the below would count the number of dates in the Inquiry Date column that have the same month as today (note, have not tested these formulas they are off the top of my head).

=COUNTIF([Inquiry Date]:[Inquiry Date], MONTH(@cell) = MONTH(TODAY())

Say you put in a Date column called Search Date that you use to specify the month you want to search for, you could use

=COUNTIF([Inquiry Date]:[Inquiry Date], MONTH(@cell) = MONTH([Search Date]@row)

I'd recommend using the MONTH() function for your cross sheet formula in a similar fashion

Sarah Zimmerman · 09/01/20

https://community.smartsheet.com/discussion/comment/261230#Comment_261230

Hi there, I got this to work with the following formula:

=COUNTIFS({Media Tracking Range 1}, @cell > DATE(2020, 1, 1), {Media Tracking Range 1}, @cell < DATE(2020, 1, 31))

SoS | Dan Palenchar · 09/01/20

@Sarah Zimmerman Glad you got it to work!

Counting and Dates

Answers

Categories