CountIf with Date range

lnelsen
lnelsen ✭✭
edited 09/06/22 in Formulas and Functions

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!

Screen Shot 2019-04-08 at 3.32.57 PM.png

Best Answer

«13456

Answers

  • Paul Newcome
    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
    lnelsen ✭✭

    Paul, thank you for feedback and tips!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help! yes

     

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

  • 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
    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)))

  • 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
    Paul Newcome ✭✭✭✭✭✭
    edited 02/21/20

    @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())))

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

  • Paul Newcome
    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.

  • 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
    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())))

  • 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
    Paul Newcome ✭✭✭✭✭✭

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

  • 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
    Paul Newcome ✭✭✭✭✭✭

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

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!