Formula for showing the age range when dates are given

[Miguelluis]

Hi,

I'm trying to create a simple table that counts dates that fall into a specific age range:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]0-10 days old[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]10-20 days old[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]Over 20 days old[/TD]
[TD]?[/TD]
[/TR]
</tbody>[/TABLE]

The easiest way would be to add a column to where the dates are and insert the networkdays formula but I need to avoid and add columns to my core data. The column I'll be counting only contains dates.

Is there a way to do this?

Thank you
Miguel

 

[Joe4]

You can use COUNTIFS to do that.
See here for an example: https://exceljet.net/formula/count-cells-between-dates

 

[Scott T]

Excel 2010
	A	B	C
1	1/5/2018
2	2/7/2018
3	2/13/2018
4	3/14/2018
5	3/30/2018
6	4/2/2018
7
8
9
10	0	10	2
11	11	20	1
12		21	3
Sheet5

Cell Formulas
Range	Formula
C10	=SUMPRODUCT(--(NETWORKDAYS($A$1:$A$6+0,TODAY())<=B10),--(NETWORKDAYS($A$1:$A$6+0,TODAY())>A10))
C11	=SUMPRODUCT(--(NETWORKDAYS($A$1:$A$6+0,TODAY())<=B11),--(NETWORKDAYS($A$1:$A$6+0,TODAY())>A11))
C12	=SUMPRODUCT(--(NETWORKDAYS($A$1:$A$6+0,TODAY())>=B12))

 

[Joe4]

Ah, I overlooked the part of Networkdays.
That may necessitate the use of SUMPRODUCT instead of COUNTIFS, like Scott showed.

 

[Miguelluis]

Thank you both, Scott's formulas work almost perfectly, however the over 20 days counts all the blanks too, is there a way to ignore blanks but use these formulas?

 

[Scott T]

Add a is not blank condition.

=SUMPRODUCT(--(NETWORKDAYS($A$1:$A$7+0,TODAY())>=B12),--($A$1:$A$7<>""))

 

[Miguelluis]

Scott T, you're an artist! Thank you.