Conditional Subtotal - Calculate min/max in column A for rows that are not empty in column B?

[unclefish]

Dear Excelists,

I'd appreciate your comments on the following problem:

I do have a data table with the project name in the 1st colum, duration of the problem in the 2nd colum and some parameter in the 3rd colum.

Project	Time	Parameter
A	2	20
A	4
B	12	15
A	24	30
B	60	40
B	12
A	48
A	4	25
A	4
A		8

I want ro review the (min/max) duration of activities for project A or B, but only including those activities that already produced some output (parameter).


For the minimum duration, I'm trying to do this by using the subtotal function =subtotal(105;B2:B11). But when i filter for project A or B, this formula returns the minimum over the entire filtered range.
However i would like to have the minmum or maximum only those cells with non-empty rows in the parameter colum (the cell with bold numbers in the above table).


If done correctly i would only include the cells highlighted in yellow (project A) or blue (project B) in my review (see the attached screenshot).
And i'd obtain the following result:
Project A: min 2, max 24
Project B: min 12, max 60

Any help is highly appreciated!

 

[DanteAmor]

Hi @unclefish

Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.​

I think you do have the maxifs function.

Try this:

Dante Amor
	A	B	C	D	E	F	G	H
1	Project	Time	Parameter			Project	Min	Max
2	A	2	20			A	2	24
3	A	4				B	12	60
4	B	12	15
5	A	24	30
6	B	60	40
7	B	12
8	A	48
9	A	4	25
10	A	4
11	A		8
Sh2

In G2 to G3:

=MINIFS($B$2:$B$11,$A$2:$A$11,$F2,$B$2:$B$11,"<>",$C$2:$C$11,"<>")

In H2 to H3:

=MAXIFS($B$2:$B$11,$A$2:$A$11,$F2,$B$2:$B$11,"<>",$C$2:$C$11,"<>")

--------------------------​

But if you don't have it, you can use the following:

Dante Amor
	A	B	C	D	E	F	G	H
1	Project	Time	Parameter			Project	Min	Max
2	A	2	20			A	2	24
3	A	4				B	12	60
4	B	12	15
5	A	24	30
6	B	60	40
7	B	12
8	A	48
9	A	4	25
10	A	4
11	A		8
Hoja1

Cell Formulas
Range	Formula
G2:G3	G2	=MIN(IF($A$2:$A$11=$F2,IF($B$2:$B$11<>"",IF($C$2:$C$11<>"",$B$2:$B$11))))
H2:H3	H2	=MAX(IF($A$2:$A$11=$F2,IF($B$2:$B$11<>"",IF($C$2:$C$11<>"",$B$2:$B$11))))
Press CTRL+SHIFT+ENTER to enter array formulas.

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[unclefish]

Hi DanteAmor!

Thank you very much for the warm welcome! And thank you even more for your comment and the provided solution.
But in my case i have to use filters (for too many reasons to be summarized here) so i think i can't use an approach with fixed criteria fields (like those in colum F of you formula).

I do know that i can calculate the min of colum B and only taking into account the rows with non-empty cells in colum C via the MINIFS formula:

=MINIFS(B2:B11,C2:C11,"<>")

But this will not change if i apply filters to colum A.
I need a way to combine the minifs function with the subtotal function.

 

[DanteAmor]

How about a help column?
In cell D1 you write the name of the project.
And in cells H1 and J1 the formulas for min and max:

Dante Amor
	A	B	C	D	E	F	G	H	I	J
1	Project	Time	Parameter	A			Min	2	Max	24
2	A	2	20	2
3	A	4		FALSE
4	B	12	15	FALSE
5	A	24	30	24
6	B	60	40	FALSE
7	B	12		FALSE
8	A	48		FALSE
9	A	4	25	4
10	A	4		FALSE
11	A		8	FALSE
12
13
14
15
Hoja1

Cell Formulas
Range	Formula
H1	H1	=SUBTOTAL(105,$D$2:$D$15)
J1	J1	=SUBTOTAL(104,$D$2:$D$15)
D2:D11	D2	=IF(AND(A2=$D$1,C2<>"",B2<>""),B2)

 

[Dave Patton]

With a Table

T202306a.xlsm
	A	B	C
1	Max	24
2	Min	2
3	Project	Time	Parameter
4	A	2	20
7	A	24	30
11	A	4	25
13	A		8
14
2d

Cell Formulas
Range	Formula
B1	B1	=SUBTOTAL(104,Table2[Time])
B2	B2	=SUBTOTAL(105,Table2[Time])

 

[Dave Patton]

If you have the Filter formula in your version of Excel, try the following:

T202306a.xlsm
	A	B	C
15	Max	24
16	Min	2
17	Project	Time	Parameter
18	A	2	20
19	A	4
20	B	12	15
21	A	24	30
22	B	60	40
23	B	12
24	A	48
25	A	4	25
26	A	4
27	A		8
2d

Cell Formulas
Range	Formula
B15	B15	=MAX(FILTER(B18:B27,(A18:A27="A")*(C18:C27>0)))
B16	B16	=MIN(FILTER(B18:B27,(A18:A27="A")*(C18:C27>0)))

 

[unclefish]

Thanks a lot, Dave!
I do understand the idea of a helper column but this approach is not applicable for my case.

The table I posted is just a small piece of a much larger table with multiple other functions. It covers data of almost 30 projects and is frequently updated with new projects/data.
For some reviews i need to include mutiple projects, i also apply multiple filters and so on... all this makes an approach with a fixed criteria cell not feasible.

 

[Fluff]

You could use a helper column like

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Project	Time	Parameter	IsVisible			Min	2	Max	60
2	A	2	20	1
3	A	4		1
4	B	12	15	1
5	A	24	30	1
6	B	60	40	1
7	B	12		1
8	A	48		1
9	A	4	25	1
10	A	4		1
11	A		8	1
Master

Cell Formulas
Range	Formula
H1	H1	=MINIFS(B2:B100,C2:C100,"<>",D2:D100,1)
J1	J1	=MAXIFS(B2:B100,C2:C100,"<>",D2:D100,1)
D2:D11	D2	=SUBTOTAL(103,A2)

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Project	Time	Parameter	IsVisible			Min	12	Max	60
4	B	12	15	1
6	B	60	40	1
7	B	12		1
Master

Cell Formulas
Range	Formula
H1	H1	=MINIFS(B2:B100,C2:C100,"<>",D2:D100,1)
J1	J1	=MAXIFS(B2:B100,C2:C100,"<>",D2:D100,1)
D4,D6:D7	D4	=SUBTOTAL(103,A4)

 

[Dave Patton]

Thanks a lot, Dave!
I do understand the idea of a helper column but this approach is not applicable for my case.

I did not use helper columns in either of the suggestions!
Did you try the suggestions?