Formulas: Aging Support tickets Calculation

[NeoSez]

I am trying to calculate tickets that are 0-30 days, 31-60, 61-90 etc...
This one is for 30 days, but everything shows as zero, which I know is wrong.
I want to calculate the average age of all tickets combined as well.
Any help would be greatly appreciated.

=COUNTIFS(SHEET2!$F:F,$A4,SHEET2!$B:$B,""=IF[B](C2<TODAY(),(IF(TODAY()-C2<=30,D2,0)),0)[/B]

 

[Eric W]

I'm a little puzzled about your ranges, but this might give you the right idea:

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H	I	J
1
2
3								# of tickets < 30 days		Average age of tickets (days)
4	X	1-Aug				x		9		26
5		2-Aug				x
6		3-Aug				x
7		4-Aug				x
8		5-Aug				x
9		6-Aug				x
10		7-Aug				x
11		8-Aug				x
12		9-Aug				x
13		10-Aug				x
14		11-Aug				y
15		12-Aug				y
16		13-Aug				x
17
18
19
20
Sheet2

Cell Formulas
Range	Formula
H4	H4	=COUNTIFS(F:F,A4,B:B,"<="&TODAY(),B:B,">="&TODAY()-30)
J4	J4	=SUMPRODUCT((TODAY()-B4:B20),--(B4:B20>0))/COUNT(B4:B20)

 

[NeoSez]

Eric W Thank you.
A little more context. It is counting the data from Sheet2. My chart and calculations are on Sheet1.

 

[Eric W]

I gathered as much. This was an example, you'd need to change the ranges, including sheet names to suit. But in particular, what do you have on Sheet1 in A4, C2, and D2? On Sheet2, what is in columns B and F? If you could use XL2BB (see the link in my signature) to post a sample of your sheet, it would be most helpful. Or at least a screen print.

 

[NeoSez]

So I got the first formula to work. Thank you!
To clarify, the formula is for under 30 days? I changed the 30 to 60, but not sure if that means 0-60 days or 30-60 days. How do I do the ranges 31-60 or over 90 days?
However, the average, I could not figure it out. I tried this but it didn't work. Where to add SHEET2!?

=COUNTIFS(SHEET2!$F:F,$A4,SHEET2!$B:$B,""=SUMPRODUCT((TODAY()-SHEET2!$B4:B20),--(SHEET2!$B4:B20>0))/COUNT(SHEET2!$B4:B20)

 

[NeoSez]

I gathered as much. This was an example, you'd need to change the ranges, including sheet names to suit. But in particular, what do you have on Sheet1 in A4, C2, and D2? On Sheet2, what is in columns B and F? If you could use XL2BB (see the link in my signature) to post a sample of your sheet, it would be most helpful. Or at least a screen print.

Sheet2 Col B=Status BTW, how do I calculate average days to complete?
Sheet2 Col B=date
Sheet2 Col A=Ticket #