COUNTIFS Function With Complex Criteria

1
votes

I'm trying to create a formula to return a count of records meeting multiple criteria and I've come up with the following so far which is not working but demonstrates a bit of what I'm after, 

=COUNTIFS(div_cde2,"UG", yr_cde2,C6&"*"-6, trm_cde2,"10", cohort_cde2,C6&"*"-6&"YY"&"FBT")

I want to define more complex criteria to accomplish the following:

  1. Subtract 6 years from the 4-digit year in C6
  2. Retain only the last 2 digits of the year resulting from that calculation
  3. Add the letters "FBF" to the two digit year to create the criteria needed for the cohort_cde defined range.

Thanks in advance.

1
exceldatecriteria

1 Answers

0
votes

The following should answer the questions you have.

  1. Subtract 6 years from the 4-digit year in C6

    C6 - 6

  2. Retain only the last 2 digits of the year resulting from that calculation

RIGHT(C6-6, 2)

  1. Add the letters "FBF" to the two digit year to create the criteria needed for the cohort_cde defined range.

RIGHT(C6-6, 2) & "FBF"

Put all these things together and you get (formula that OP ultimately used)

=COUNTIFS(div_cde2,"UG",yr_cde2,C6-6,trm_cde2,"10",cohort_cde2,RIGHT(C6-6, 2)&"FBF")