Finding minimum and maximum values based on multiple criteria and filtered data

**jndreece** · 05-12-2014, 04:06 PM

I have a request to find the min and max values from a range. First the list needs to be narrowed down using criteria based on other columns of data. It then needs to only show the data in that has been filtered. Finding the min and max based on the other columns of data I can find using Min(if((range1="x")*(range2="y"),range3)) and I can find the filtered data using subtotal(105,range3). Doing a search I found using SUMPRODUCT would work which it does if there is only one criteria. Adding the second range of criteria causes the values to be added together. Any ideas?

**FDibbins** · 05-12-2014, 04:10 PM

hard to say without seeing samples of what you have and what you want

**jndreece** · 05-12-2014, 05:08 PM

Originally Posted by FDibbins

hard to say without seeing samples of what you have and what you want

Well if I could find the paper clip I'd attach a worksheet. Here's a picture of what I mean.

Filtered Worksheet.jpg

**Tony Valko** · 05-12-2014, 07:53 PM

Here's an example...

Data Range

	A	B	C	D	E	F	G
4	------	------	------	------	------	------	------
5	Region	Status	Value		North	Yes	39
6	East	No	52
7	East	No	86
8	East	No	90
9	East	Yes	76
10	North	No	8
11	North	No	94
12	North	Yes	39
13	North	Yes	81
14	South	No	3
15	South	No	10
16	South	No	31
17	South	Yes	27
18	South	Yes	31
19	South	Yes	74
20	South	Yes	75
21	South	Yes	83
22	West	No	10
23	West	No	31
24	West	No	33
25	West	No	93
26	West	Yes	24
27	West	Yes	45

We want the min value for Region = North and Status = Yes of a filtered (or unfiltered) list.

This array formula** entered in G5:

=MIN(IF(SUBTOTAL(2,OFFSET(C6,ROW(C6:C27)-ROW(C6),0)),IF(A6:A27=E5,IF(B6:B27=F5,C6:C27))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

I have the data sorted just to make it easy to find the correct result.

**jndreece** · 05-13-2014, 10:35 AM

Thanks Tony. That worked perfectly! Not sure how to mark the correct answer on here. Thanks again.

**Tony Valko** · 05-13-2014, 10:42 AM

You're welcome. Thanks for the feedback!

We don't mark correct answers here. We just ask that if your question was solved then mark the thread as being solved which you have already done!

Finding minimum and maximum values based on multiple criteria and filtered data

LinkBack

Thread Tools

Rate This Thread

Display

Finding minimum and maximum values based on multiple criteria and filtered data

Re: Finding minimum and maximum values based on multiple criteria and filtered data

Re: Finding minimum and maximum values based on multiple criteria and filtered data

Re: Finding minimum and maximum values based on multiple criteria and filtered data

Re: Finding minimum and maximum values based on multiple criteria and filtered data

Re: Finding minimum and maximum values based on multiple criteria and filtered data

Thread Information

Users Browsing this Thread

Similar Threads

Minimum, Maximum, Mean and Median for data set with Multiple Criteria

Maximum and Minimum values from data stream

Finding minimum value in a column based on multiple text criteria in other columns

[SOLVED] vba to get the minimum and maximum values based on a unique ID

Maximum & Minimum values - filtered columns across sheets

Bookmarks

Bookmarks

Posting Permissions