Counting total specific drop-down option between 2 dates

E_PieperE_Pieper
edited 12/09/19 in Formulas and Functions
08/30/18 Edited 12/09/19

Hello!

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!

-Beth

Comments

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

    Sean

    Untitled.png

    Untitled2.png

  • 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 NewcomePaul 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.

    thinkspi.com

Sign In or Register to comment.