Counting total specific drop-down option between 2 dates

E_Pieper

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

sean59916

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

E_Pieper

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

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.