Return a list of dates and form average from a table

[mark hansen]

I need help with a complex formula that will search four columns of a table and create a list of dates that match that criteria. I figure it will an array formula and I have found some examples when the criteria is 1 column, but not 4.

The Table is "Table1"
The column with the date I need in the list is [Date of Proc]

The Criteria is
[Name] = AE35
[PGY]= AU35
[AY]BI35
[Proc]=DB58

The list will start in DB60.

I also need to use the same formula to pull a value from another column, but I think I can change the [Date of proc] field for [Avg of Form] column to get the value in that column. I'll be making a graph when the resulting two columns.

Thanks for any help.
Mark

 

[RasGhul]

Hi Mark,

Can you post some sample data just to confirm the header names of your table, also to clarify you want to enter your criteria in the above ranges and return the values starting from DB60?

 

[RasGhul]

Maybe this, paste formulas & drag down as far as needed.

Just change the ranges to suit your data.

Book1
	A	B	C	D	E	F
1	Criteria	Name	PGY	AY	Proc	Countifs
2		Name1	PGY1	AY1	Proc1	4
3
4	Date of Proc	Name	PGY	AY	Proc	Avg of form
5	01-10-19	Name1	PGY1	AY1	Proc1	1
6	02-10-19	Name2	PGY2	AY2	Proc2	2
7	03-10-19	Name3	PGY3	AY3	Proc3	3
8	04-10-19	Name4	PGY4	AY4	Proc4	4
9	05-10-19	Name1	PGY1	AY1	Proc1	5
10	06-10-19	Name6	PGY6	AY6	Proc6	6
11	07-10-19	Name1	PGY1	AY1	Proc1	7
12	08-10-19	Name8	PGY8	AY8	Proc8	8
13	09-10-19	Name9	PGY9	AY9	Proc9	9
14	10-10-19	Name1	PGY1	AY1	Proc1	10
15
16	Dates	Avg
17	01-10-19	1
18	05-10-19	5
19	07-10-19	7
20	10-10-19	10
Sheet1

Cell Formulas
Range	Formula
F2	=COUNTIFS(Table1[Name],B2,Table1[PGY],C2,Table1[AY],D2,Table1[Proc],E2)
A17	{=IF(ROWS(A$17:A17)>$F$2,"",INDEX(Table1[Date of Proc],SMALL(IF(Table1[Name]&Table1[PGY]&Table1[AY]&Table1[Proc]=$B$2&$C$2&$D$2&$E$2,ROW(Table1[Date of Proc])-ROW(Table1[#Headers])),ROWS(A$17:A17))))}
B17	{=IF(ROWS(B$17:B17)>$F$2,"",INDEX(Table1[Avg of form],SMALL(IF(Table1[Name]&Table1[PGY]&Table1[AY]&Table1[Proc]=$B$2&$C$2&$D$2&$E$2,ROW(Table1[Date of Proc])-ROW(Table1[#Headers])),ROWS(B$17:B17))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[mark hansen]

thanks so much for your help Ras, I really appreciate it. These formula are working out well!!!