CODEWITHSUNDEEP
exceldate
user3795203
user3795203

Reputation: 55

COUNTIFS formula to count unique records where range contains dates within specified year criteria

I've been Googling for hours trying to create a COUNTIFS formula that will:

  1. count the unique records in a table range
  2. that have dates that fall within 4 years after
  3. a year in a referenced cell

This is what I have so far: =COUNTIFS(dte_degr_conferred2,"<="&YEAR('Student Success and Progress'!$G$1)+4) but it is not correct and I've tried several variations

Please note: dte_degr_conferred2 column contains dates, 'Student Success and Progress'!$G$1 contains a 4-digit year

Many Thanks

Upvotes: 0

Views: 668

Answers (4)

Rick Hitchcock
Rick Hitchcock

Reputation: 35670

The "count unique" requirement makes this an interesting question.

Here's what I've come up with:

=SUM(
  IF(
   FREQUENCY(
    IF((YEAR(ddc)>=ssp!$G$1) + (YEAR(ddc)<ssp!$G$1+4) = 2, ddc, ""),
    IF((YEAR(ddc)>=ssp!$G$1) + (YEAR(ddc)<ssp!$G$1+4) = 2, ddc, "")
   )>0,
   1
  )
 )

... where ddc is dte_degr_conferred2 and ssp is 'Student Success and Progress'. (When posting to SO, it's helpful to reduce a question to its essentials, because that makes it easier to respond to, as well as making it more universally useful.)

Enter as an array formula: Ctrl + Shift + Enter

Example

enter image description here

In this example, there are three distinct dates that match the criteria: 05/12/2014, 09/12/2014, and 05/26/2012.

How it works:

When creating array formulas, it can be useful to work out the calculations separately before joining them in an array. I've done so here:

enter image description here

Column C is =(YEAR(A1)>=$B$1) + (YEAR(A1)<$B$1+4) (copied down). In Excel, TRUE is 1 and FALSE is 0, so you can add boolean values together. If A1 is between B1 and B1+4, both operands are TRUE, and TRUE + TRUE = 2.

Column D is =IF(C1=2,A1,"") (copied down). This grabs the data that matches the criteria. (I formatted as numbers to highlight the fact that Excel stores dates as numbers.)

Column E is =SUM(IF(FREQUENCY(D1:D10,D1:D10)>0,1)). For an explanation of this method, see http://office.microsoft.com/en-us/excel-help/count-occurrences-of-values-or-unique-values-in-a-data-range-HP003056118.aspx

My solution at top combines all this into a single array formula.

Upvotes: 0

pnuts
pnuts

Reputation: 59485

I have an even more simplistic interpretation: 

=COUNTIF(dte_degr_conferred2,">="&DATE('Student Success and Progress'!$G$1+4,1,1))

Upvotes: 0

CallumDA
CallumDA

Reputation: 12113

Trying to work out exactly what you need (you might be overcomplicating). Here is an example of using the COUNTIFS formula to compare dates. This formula was entered into cell E6 with the setup as below:

=COUNTIFS($B$2:$B$6,"<="&E3+4,$B$2:$B$6,">="&E3)

enter image description here

Upvotes: 0

XOR LX
XOR LX

Reputation: 7762

Perhaps you mean this array formula**:

=SUM(IF(FREQUENCY(IF(dte_degr_conferred2<>"",IF(YEAR(dte_degr_conferred2)='Student Success and Progress'!G1,dte_degr_conferred2)),dte_degr_conferred2),1))

Regards

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

Upvotes: 0

Related Questions