Counting total specific drop-down option between 2 dates

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

Find more posts tagged with

Formulas

Legal

Comments

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Is there a way to create a 3-digit padding for numbers
Hi all, and thanks in advance! I wanted to know if there was any way to create "padding" in Smartsheet. Basically I have account numbers, and a handful are still 2-digit, so they appear as TDL-17, TDL-67, etc. Most of the accounts are 3-digit numbers like TDL-180. I want something that will recognize whether it is 2 or 3…
Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?