Counting total specific drop-down option between 2 dates

E_Pieper · 08/30/18

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 · 08/30/18

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 · 09/04/18

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 · 09/04/18

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.

Counting total specific drop-down option between 2 dates

Comments

Help Article Resources

Categories