Formula to Filter 1 or more columns based on criteria and then perform basic math on another column (based on the filter)

[queysoft]

HI there - So, I don't have O365 subscription (yet) - trying to get work to buy it for us.

Column A = List of words (example Apple.....Pear.....Orange)
Column B = List of words (example Apple.....Pear.....Orange)
Column C = A number (example 123, 432, 734......n)

In a cell, I would like to automate (or at least enter a formula) to Filter column A on a certain word (Apple) and then either AVERAGE / MAX / MIN the filtered values for the word Apple.
Then I would like another formula to do the same using values in column A and B.

It would be nice to make this into a button to click to display the information- Is that possible without using VBA?

Thank you.

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F
1
2	Apple		92			Apple
3	Pear		80		Average	69.16667
4	Orange		65		Max	92
5	Apple		39		Min	39
6	Apple		66
7	Apple		84
8	Apple		76
9	Pear		44
10	Pear		82
11	Orange		15
12	Orange		50
13	Pear		63
14	Apple		58
15
Main

Cell Formulas
Range	Formula
F3	F3	=AVERAGEIFS(C2:C100,A2:A100,F2)
F4	F4	=MAX(IF(A2:A100=F2,C2:C100,""))
F5	F5	=MIN(IF(A2:A100=F2,C2:C100,""))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[queysoft]

This looks perfect - Is it possible to enter multiple entries? So, F2 says APPLE but what if i want F3 to say PEAR as well? Can i Put an AND Statement in somewhere (like below)

=AVERAGEIFS(C2:C100,A2:A100,F2&F3)
=MAX(IF(A2:A100=F2&A2:A100=F3,C2:C100,""))

 

[Fluff]

Assuming you mean OR not AND, then

+Fluff 1.xlsm
	A	B	C	D	E	F
1
2	Apple		92		Pear	Apple
3	Pear		80		Average	66.2
4	Orange		65		Max	92
5	Apple		39		Min	22
6	Apple		66
7	Apple		84
8	Apple		76
9	Pear		22
10	Pear		82
11	Orange		15
12	Orange		50
13	Pear		63
14	Apple		58
15
Main

Cell Formulas
Range	Formula
F3	F3	=AVERAGE(IF((A2:A100=E2)+(A2:A100=F2),C2:C100,""))
F4	F4	=MAX(IF((A2:A100=E2)+(A2:A100=F2),C2:C100,""))
F5	F5	=MIN(IF((A2:A100=E2)+(A2:A100=F2),C2:C100,""))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[queysoft]

Thank you - Let me give this a go. I thank you for your time!!

 

[Fluff]

You're welcome.

 

[queysoft]

Hmm - I am getting #value.....then i realised one of the criteria boxes is a formula (hence)........I can easily solve this with a copy / PS......However, I am wondering - do you know of an easy formula to copy the value of a cell with a formula in it without using VBA? So cell A1 has a formula and i want B1 to show the actual value of A1?

 

[Fluff]

It doesn't matter if E2 or F2 are formula results, the formulae I provided will still work.

 

[queysoft]

Hmm - I can get some to work but not others - I must be doing something incorrect. Let me provide some screen shots if you don't mind.........

 

[Fluff]

It would be better if you could supply sample data using the XL2BB add-in.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.