CountIf with Date range

lnelsen

I am trying to count the number of requests types between a date range.  This is what I thought would work:  

=COUNTIF(({Request Tracker Range 4 - Request Type}, Category5), AND({Request Tracker Range 3 - Date Requested}, DATE(2019, 01, 02:2019, 03, 14)))

I am using this formula to count the entire column =COUNTIF(({Request Tracker Range 4 - Request Type}, Category5) and it works fine.  When I throw the date range into the mix, I am no longer getting results.

Any suggestions would be appreciated!

Paul Newcome

@Kendra P You would need to use an AND function. I also suggest referencing a month and year number as opposed to two separate dates (in my opinion it is easier to maintain/update).

=COUNTIFS([Date Due]:[Date Due], AND(IFERROR(MONTH(@cell), 0) = 8, IFERROR(YEAR(@cell), 0) = 2022))

Paul Newcome

A few issues with your above formula.

1. When using more than one set of criteria, you need to use a COUNTIFS instead.

2. You are closing the COUNTIF function before the additional criteria which means it is not being included in the calculations and is also not the correct syntax (thus the error).

3. When using the AND function within a COUNTIF(S) or SUMIF(S), you establish the range first and then use the AND function for the criteria along with @cell references.

4. When using multiple dates, you need to use multiple DATE functions.

Give something like this a shot...

=COUNTIFS({Request Tracker Range 4 - Request Type}, Category5, {Request Tracker Range 3 - Date Requested}, AND(@cell >= DATE(2019, 01, 02), @cell <= DATE(2019, 03, 14)))

lnelsen

Paul, thank you for feedback and tips!

Paul Newcome

Happy to help!

 

Let me know if you need any help getting something working the right way for you.

Jenelle Gullickson

I'm having a similar problem.

I am creating a YTD summary of report deliveries (we are a test laboratory and our deliverables are reports)

Range 2 is a company location "EMP"

Range 3 is a date field of when a report was formally released to a customer

Goal is to count the number of reports for each month by Company location (we have 2 locations EMP and MLB) (The formula is me trying to get a January summary of number of reports for each location working).

I get a #UNPARCABLE error.

Here is my formula.

=COUNTIFS({EMP MLB Report Tracking Range 2}, EMP, {EMP MLB Report Tracking Range 3}, AND(@cell >= DATE(2020, 01, 01), @cell <= DATE(2020, 01, 31)))

Here is the table its pulling from (location is

Your assistance is greatly appreciated!!

Thank you

Jenelle Gullickson - Element Materials Technology

Paul Newcome

You are missing quotes from around "EMP".

=COUNTIFS({EMP MLB Report Tracking Range 2}, "EMP", {EMP MLB Report Tracking Range 3}, AND(@cell >= DATE(2020, 01, 01), @cell <= DATE(2020, 01, 31)))

Sales Support

Hi -

I am trying to following your wonderful instructions. I am simply just trying to count how many times I have contacted a rep "this week". I add dates in a column when I reached out. I want to then add this Metric in a Dashboard. Is there a way to have this dynamic to where it will automatically show numbers for the current week or do I manually enter the dates in?

The Column = Last Date Rep Contacted (I also will duplicate the formula for how many times I contacted a Customer, etc.)

Formula: =COUNTIFS([Last Date Rep Contacted]1:[Last Date Rep Contacted]1021, AND(@cell >= DATE(2020, 2, 17), @cell <= DATE(2020, 2, 23)))

Thank you for your time.

Paul Newcome

@Sales Support

You could use a WEEKNUMBER and YEAR function to compare to the current week number and year. This does use the TODAY() function, so you will need to activate the sheet regularly to have the metrics updated. The WEEKNUMBER function also pulls Monday - Sunday instead of Sunday - Saturday.

The formula itself would look something like this...

=COUNTIFS([Last Date Rep Contacted]1:[Last Date Rep Contacted]1021, AND(IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))

Sales Support

Sorry I am not educated on this but do I need fill in the Weeknumber, Year or Today dates?

Paul Newcome

As long as the range is correct (column name(s) and row numbers), you should be able to drop in exactly what I have posted.

Sales Support

Thank you! I had the cell only fixed for dates and I pasted it in a regular cell and it pulled up just fine! Is there a way to put this so I can reference the number but not on this sheet?

I basically want to use the Metric widget to add to a dashboard on how many accounts we contacted, etc. Right now I have to add this at the bottom of the sheet and pull the number from that Master sheet right?

Thank you so much for your time and help.

Paul Newcome

There are many different options for pulling this to reflect in a Metric Widget. One option would be to create a Sheet Summary field, drop the formula in there, then reference that field in your widget.

To pull this data onto another sheet, you would start typing out the formula until you get to here...

=COUNTIFS(

Then just below where you are typing, you should see a helper box that has a link in it that says "Reference another sheet". You would click on this link, select the appropriate sheet, select the desired range (click on the column header to select the entire column if needed), then click the button in the bottom right corner that says "Insert Reference".

Now it should look something like this...

=COUNTIFS({Other Sheet Name Range 1}

Finish typing out the rest of the formula, and the end result would look something like this:

=COUNTIFS({Other Sheet Name Range 1}, AND(IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))

Julius Benn Sabeniano

https://community.smartsheet.com/discussion/comment/240847#Comment_240847

Hi Paul, You have mentioned WEEKNUMBER pulls data from Monday-Sunday. Is there a way or other formula to make WEEKNUMBER pulls data from Saturday to Friday, that our usual cut-off.

Thank you in advanced.

Paul Newcome

@Julius Benn Sabeniano How do you need to use this? What type of metrics are you trying to pull?

Julius Benn Sabeniano

Hi Paul, I have a weekly report that needs to count all the leads per lead source and my source sheet is pulling the data from google sheets. To count the report automatically from the sheet source, I am using this formula. Select week column dictates the week number that I need. As you have mentioned, WEEKNUMBER pulls Monday to Friday, but this morning my manager asked to align this formula to our cut-off which is Saturday to Friday to tally on our reporting.

=COUNTIFS({CD1}, IFERROR(WEEKNUMBER(@cell), 0) = [Select Week]$1, {LS1}, [Lead Source]$5)

Is there a way to make the pulling of WEEKNUMBER function to Saturday to Friday?Any formula or helping columns needed? by the way source sheet is untouchable.

Paul Newcome

I assume that {CD1} is a date type column?

Exactly what is in the [Select Week]$1 cell?