Please help me to get with formula to get minimum & maximum with different criteria from below table:

[afmrubayat]

Minimum & Maximum of feeding cost required in H2 column matching type of animal input given in E2 with specified Beginning Date in F2 and End Date in G2
	A		B		C	D	E		F		G		H		I
1	Date		Type of Animal		Feeding cost		Type of Animal		Beginning Date		End Date		Min Cost		Max Cost
2	1-Oct-16		Cow		23		Cat		2-Oct-16		4-Oct-16		24		25
3	2-Oct-16		Cow		24
4	3-Oct-16		Cow		25
5	4-Oct-16		Cow		30
6	5-Oct-16		Cow		20
7	1-Oct-16		Dog		23
8	2-Oct-16		Dog		24
9	3-Oct-16		Dog		25
10	4-Oct-16		Dog		30
11	5-Oct-16		Dog		20
12	1-Oct-16		Cat		23
13	2-Oct-16		Cat		24
14	3-Oct-16		Cat		25
15	4-Oct-16		Cat		30
16	5-Oct-16		Cat		20
17	1-Oct-16		Bird		23
18	2-Oct-16		Bird		24
19	3-Oct-16		Bird		25
20	4-Oct-16		Bird		30
20	5-Oct-16		Bird		20

<colgroup><col span="16"></colgroup><tbody>
</tbody>

 

[4everGS]

Hi,

If I understood you correctly, you need formula in column G & H for min and max costs as per animal chosen between the date ranges specified.

This is what I came up with;

Paste this in column M for Minimum calculation .. MIN(IF(I3<=$A$3:$A$22,IF(K3>=$A$3:$A$22,IF(G3=$C$3:$C$22,$E$3:$E$22))))

Maximum calculation =MAX(IF(I3<=$A$3:$A$22,IF(K3>=$A$3:$A$22,IF(G3=$C$3:$C$22,$E$3:$E$22))))

Hope this helps.

 

[Aladin Akyurek]

In H2 control+shift+enter, not just enter:

=MIN(IF($B$2:$B$21=E2,IF($A$2:$A$21>=F2,IF($A$2:$A$21<=G2,$C$2:$C$21))))

In I2 repeat this formula but replace MIN with MAX.

 

[4everGS]

Yes, Curly brackets were not copied in my post.

Tesekkurler!

 

[cnestg8r]

Dates also

E2: {=MIN(IF($D$2=$B$2:$B$21,$A$2:$A$21))}
F2: {=MAX(IF($D$2=$B$2:$B$21,$A$2:$A$21))}
G2: {=MIN(IF($D$2=$B$2:$B$21,$C$2:$C$21))}
H2: {=MAX(IF($D$2=$B$2:$B$21,$C$2:$C$21))}

 

[afmrubayat]

Thanks a lot. But is any formula without using ctrl+shift+enter. If the information contain some blank row it giving zero how to ignore them. Actual data is around more than 30,000 row data how to use this formula for that?

 

[Aladin Akyurek]

Thanks a lot. But is any formula without using ctrl+shift+enter. If the information contain some blank row it giving zero how to ignore them. Actual data is around more than 30,000 row data how to use this formula for that?

Control+shift+enter, not just enter:

=MIN(IF($B$2:$B$21=E2,IF($A$2:$A$21>=F2,IF($A$2:$A$21<=G2,IF(ISNUMBER($C$2:$C$21),$C$2:$C$21)))))

No edit is required for the max value... again control+shift+enter:

=MAX(IF($B$2:$B$21=E2,IF($A$2:$A$21>=F2,IF($A$2:$A$21<=G2,$C$2:$C$21))))

 

[afmrubayat]

=MIN(IF($C$2:$C$397095=K10,IF($B$2:$B$397095>=L10,IF($B$2:$B$397095<=M10,IF(ISNUMBER($D$2:$D$397095),$D$2:$D$397095)))))
formula not working please check. I have 397095 row in actual data

 

[Aladin Akyurek]

=MIN(IF($C$2:$C$397095=K10,IF($B$2:$B$397095>=L10,IF($B$2:$B$397095<=M10,IF(ISNUMBER($D$2:$D$397095),$D$2:$D$397095)))))
formula not working please check. I have 397095 row in actual data

Control+shift+enter means: Press down the control and the shift keys at the same time while you hit the enter key. If done properly, Excel itself will put a pair of { and } around the formula in recognition.

By the way, the ranges are huge, so performance won't be great.

 

[afmrubayat]

Sorry not working