AverageIF that contain a text for visible/filtered cells only

[floW_5]

Sorry, newbie here. I've been looking around (probably not hard enough) for the most appropriate formula with the AVERAGEIF function to work with filtered/visible cells only.

These are the two formula i am using atm which is working perfectly fine to show all data. But not when i filter out certain criteria because it keeps recognizing all cell within the table. Screenshot also attached.
=IFERROR(AVERAGEIF(AA:AA,"P",AD:AD),"")
and
=IFERROR(AVERAGEIF(AA:AA,"P",AE:AE),"")

I am truly sorry if this post have been made many times before me. I have tried a bunch of different solution from here & reddit and maybe I'm inputting something wrong but nothing seems to work for me yet.

 

[Peter_SSs]

Welcome to the MrExcel board!

To average filtered values try something like this. I recommend that you do not use whole column references.

=AGGREGATE(1,5,AD2:AD1000)

 

[floW_5]

Welcome to the MrExcel board!

To average filtered values try something like this. I recommend that you do not use whole column references.

=AGGREGATE(1,5,AD2:AD1000)

Hey, thank you for the super fast reply. Unfortunately this formula does not include the IF criteria. I'm trying to get it to average out only rows that contains the letter "P" with the second column number values. Hope this makes sense

 

[floW_5]

Welcome to the MrExcel board!

To average filtered values try something like this. I recommend that you do not use whole column references.

=AGGREGATE(1,5,AD2:AD1000)

Could you also please tell me why i shouldn't use the whole column reference?

 

[Peter_SSs]

Unfortunately this formula does not include the IF criteria. I'm trying to get it to average out only rows that contains the letter "P" with the second column number values.

I thought that must have been the column that you were filtering on since that was the only column in your image with a filter drop-down showing. If you are filtering on that column then filtering for "P" should mean that the AGGREGATE formula would automatically only average the "P" values For example, here is my small example with nothing filtered.

24 01 04.xlsm
	Z	AA	AB	AC	AD
1	Average of col AD	Hdr1	Hdr2	Hdr3	Hdr4
2	4	P	9		4
3		F	8		5
4		P	8		6
5		F	2		2
6		P	3		3
Av Filtered

Cell Formulas
Range	Formula
Z2	Z2	=AGGREGATE(1,5,AD2:AD1000)

Then filter col AA for "P" values

24 01 04.xlsm
	Z	AA	AB	AC	AD
1	Average of col AD	Hdr1	Hdr2	Hdr3	Hdr4
2	4.333333333	P	9		4
4		P	8		6
6		P	3		3
7
Av Filtered

Cell Formulas
Range	Formula
Z2	Z2	=AGGREGATE(1,5,AD2:AD1000)

Could you also please tell me why i shouldn't use the whole column reference?

For some formulas it does not matter but others will do some calculation(s) for all 1 million plus cells in the column which can make the calculation very slow. If you only have a few hundred or a few thousand rows, why make Excel calculate all 1,048,576 cells (possibly several times, depending on the formula used)?

It looks like your image shows a formal Excel table at the left with only about 20 rows. Is that the case?

Perhaps you could tell us a bit more about exactly what you have, where it is, and what you are trying to do?
You might also give us some sample data to actually test with using XL2BB?

 

[floW_5]

I thought that must have been the column that you were filtering on since that was the only column in your image with a filter drop-down showing. If you are filtering on that column then filtering for "P" should mean that the AGGREGATE formula would automatically only average the "P" values For example, here is my small example with nothing filtered.

24 01 04.xlsm
	Z	AA	AB	AC	AD
1	Average of col AD	Hdr1	Hdr2	Hdr3	Hdr4
2	4	P	9		4
3		F	8		5
4		P	8		6
5		F	2		2
6		P	3		3
Av Filtered

Cell Formulas
Range	Formula
Z2	Z2	=AGGREGATE(1,5,AD2:AD1000)

Then filter col AA for "P" values

24 01 04.xlsm
	Z	AA	AB	AC	AD
1	Average of col AD	Hdr1	Hdr2	Hdr3	Hdr4
2	4.333333333	P	9		4
4		P	8		6
6		P	3		3
7
Av Filtered

Cell Formulas
Range	Formula
Z2	Z2	=AGGREGATE(1,5,AD2:AD1000)

For some formulas it does not matter but others will do some calculation(s) for all 1 million plus cells in the column which can make the calculation very slow. If you only have a few hundred or a few thousand rows, why make Excel calculate all 1,048,576 cells (possibly several times, depending on the formula used)?

It looks like your image shows a formal Excel table at the left with only about 20 rows. Is that the case?

Perhaps you could tell us a bit more about exactly what you have, where it is, and what you are trying to do?
You might also give us some sample data to actually test with using XL2BB?

Data1.xlsx
	A	B	C	D	E	F	G	H
3							Avg1	Avg2
4							5.89	7.21
5		Column4	Column5	Column21	Column22	Pass or Fail	Difference1	Difference2
6				34.65	151.98	P
7				41.64	448.92	F
8				78.79	511.20	P
9		80.65	519.64	79.12	634.78	F	1.02	0.82
10		9.84	114.11	63.95	289.90	P	0.15	0.39
11				31.87	261.15	P
12		86.01	247.72	3.26	9.19	P	26.38	26.95
13				57.13	265.97	F
14		162.82	994.83	85.39	462.84	P	1.91	2.15
15				113.72	231.42	P
16				131.11	879.80	F
17		28.14	91.55	82.94	169.86	P	0.34	0.54
18		131.25	879.80	8.10	34.73	P	16.21	25.34
19				37.82	123.59	P
20				99.33	99.43	F
21				47.11	227.55	P
22		48.85	276.33	48.33	250.68	P	1.01	1.10
23		5.22	36.82	31.29	124.21	P	0.17	0.30
24		82.23	160.08	89.06	171.89	P	0.92	0.93
25				61.49	381.22	F
MASTER DATA

Cell Formulas
Range	Formula
G4	G4	=IFERROR(AVERAGEIF(F6:F1000,"P",G6:G1000),"")
H4	H4	=IFERROR(AVERAGEIF(F6:F1000,"P",H6:H1000),"")
G6:H25	G6	=IF(AND(B6<>"",D6<>""),(B6/D6),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F6:F1048576	Cell Value	contains "F"	text	NO
F6:F1048576	Cell Value	contains "P"	text	NO

Sorry had to play around a little more with the sheet and install xl2bb. But here is the sample data. hope this helps.

 

[Peter_SSs]

Glad that you got XL2BB working.

What about ..

It looks like your image shows a formal Excel table at the left with only about 20 rows. Is that the case?

and

Perhaps you could tell us a bit more about exactly .... what you are trying to do?

 

[floW_5]

Glad that you got XL2BB working.

What about ..

and

So the formula in G4 works perfectly for what i want it to do with AVERAGEIF function. But when i do want to filter out some criteria which essentially removes certain data base what criteria is filtered out the value in G4 still shows the same average value. (Does not change)

The formula is to only average up value from "Difference1" IF column F contains the text "P".

 

[floW_5]

Glad that you got XL2BB working.

What about ..

and

Yes its about 20 row for now and will be adding on a daily basis. Its an ongoing data collection.

=AVERAGEIF(F6:F1000,"P",G6:G1000)
Turn this formula to work on only visible cells when i filter out some data?

 

[Peter_SSs]

What about ..

It looks like your image shows a formal Excel table at the left with only about 20 rows. Is that the case?

If so, what is the name of the table?
What columns does the entire table occupy?