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

#### afmrubayat

##### New Member
 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>

### Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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.

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.

Yes, Curly brackets were not copied in my post.

Tesekkurler!

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))}

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?

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))))

=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

=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.

Sorry not working

Replies
17
Views
1K
Replies
5
Views
826
Replies
6
Views
690
Replies
0
Views
325
Replies
1
Views
299

1,196,047
Messages
6,013,085
Members
441,747
Latest member
darkman77

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back