Counting total specific drop-down option between 2 dates

edited 12/09/19 in Formulas and Functions


The sheet that I am working with is used to track agreements my department works with. Two of the data points (among many) that we capture is the agreement type within a drop-down and the signature date. I would like to capture the total "Amendment"'s from the drop-down list with a signature date that fall in our Fiscal Year (10/1/2017 through 9/30/18). 

Ideally I would like to put the calculation in a separate sheet from the "agreements database" I am keeping. I have figure out how to reference another sheet. I have also figured out how to calculate:

-Between dates COUNTIF({signature date},<=Date(2018,09,30), {signature date},>=DATE(2017,10,01)) 

- How to count the total within a drop-down COUNTIF([Agreement Type]:Agreement Type],"Amendment")

However, I cannot figure out how to combine the two or if there is another way. Please let me know if anyone can give me some insight. Thanks in advance!




  • Hi Beth - 

    Change the formula to a COUNTIFS for multiple criteria

    Try this: 

    =COUNTIFS([signature date]1:[signature date]8, <=DATE(2018, 9, 30), [signature date]1:[signature date]8, >=DATE(2017, 10, 1), [Agreement Type]1:[Agreement Type]8, "Ammendment")

    Hope that helps!




  • Thanks Sean. I tried that, but it did not work. I am wondering if having the amendments in Children rows makes a different. Below is the equation I am currently using and I get the "#unparseable" issue. 

    =COUNTIFS({CRADA Database Range 3}1:{CRADA Database Range 3}76, <=DATE(2018, 09, 30), {CRADA Database Range 3}1:{CRADA Database Range 3}76, >=DATE(2017, 10, 01), {CRADA Database Range 2}1:{CRADA Database Range 2}76, "Amendment")


    Any insight?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    When using X-sheet references, you cannot specify row numbers like this: {Sheet Name Range 1}1:{Sheet Name Range 1}76. When selecting your X-sheet reference, you have to highlight those specific cells. The result would be {Sheet Name Range 1}. You would then use ONLY that in your formula. Something along the lines of:


    =COUNTIFS({CRADA Database Range 3}, <=DATE(2018, 09, 30), {CRADA Database Range 3}, >=DATE(2017, 10, 01), {CRADA Database Range 2}, "Amendment")


    For your X-sheet references in your formula, select the cells in rows 1 - 76 in the appropriate column. Of course the range number will be different, but the above is what it should look like.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!