Fin Min value from column header criteria

[cshetty]

Dear All

I have a data layout like below.

Cell A1, C1, E1, G1....... coloumn header "UNIT"
Cell B1,D1,F1,H1 ...... Column Header "Amount"

Data starts from A2

in Cell S2 I want the minimum unit value for the range A2:R2


Help me to find the minimum unit value, ignoring the Amount column.



.

 

[Habtest]

Hi, try this:

Book4
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Unit	Amount	Unit	Amount
2	10	1	40	4															10
3	20	2	50	5															20
4	30	3	60	6															30
Sheet1

Cell Formulas
Range	Formula
S2:S4	S2	=AGGREGATE(15,6,A2:R2/($A$1:$R$1="Unit"),1)

 

[cshetty]

Hi, try this:

Book4
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Unit	Amount	Unit	Amount
2	10	1	40	4															10
3	20	2	50	5															20
4	30	3	60	6															30
Sheet1

Cell Formulas
Range	Formula
S2:S4	S2	=AGGREGATE(15,6,A2:R2/($A$1:$R$1="Unit"),1)

Thank you Sir..

Wanted to ignore if the Unit Value is Zero. So tweaked your formula to =AGGREGATE (15,6, A2:R2/ (($A$1: $R$1="Unit") *(A2:R2>0)),1)