# Excel Small If Formula For Multiple Criterias

#### hrayani

##### Well-known Member
Hello Friends,

I am using the below formula to extract the smallest value from the data

Code:
``=SMALL(IF((\$G\$2=gsm_pc_weight_ref_no)*(\$J\$2=gsm_pc_weight_design_color_name)*(\$H\$2>=gsm_pc_weight_date)*(\$I\$2<=gsm_pc_weight_date),gsm_pc_weight_value,""),ROW(1:1))``
CTL+SHF+ENT

Cells H2 & I2 in the formula represents dates (From - To)

The problem I am having is when both dates are equal than the formula works fine.
But as soon as I change the From date or To date then the formula gives error #NUM !

Am I doing anything wrong

Any help would be appreciated.

#### Marcelo Branco

##### MrExcel MVP
Try changing

(\$H\$2>=gsm_pc_weight_date)*(\$I\$2<=gsm_pc_weight_date)

to

(\$H\$2<=gsm_pc_weight_date)*(\$I\$2>=gsm_pc_weight_date)

M.

#### DanteAmor

##### Well-known Member
Hello Friends,

I am using the below formula to extract the smallest value from the data

Code:
``=SMALL(IF((\$G\$2=gsm_pc_weight_ref_no)*(\$J\$2=gsm_pc_weight_design_color_name)*(\$H\$2[SIZE=3][B][COLOR=#ff0000]<[/COLOR][/B][/SIZE]=gsm_pc_weight_date)*(\$I\$2[SIZE=3][B][COLOR=#ff0000]>[/COLOR][/B][/SIZE]=gsm_pc_weight_date),gsm_pc_weight_value,""),ROW(1:1))``
CTL+SHF+ENT

Cells H2 & I2 in the formula represents dates (From - To)

The problem I am having is when both dates are equal than the formula works fine.
But as soon as I change the From date or To date then the formula gives error #NUM !

Am I doing anything wrong

Any help would be appreciated.
I'm not sure, but the date should be between From and To, change the signs.

#### hrayani

##### Well-known Member
Hi Marcelo & DanteAmor

Thanks for the reply. I won’t be having access to my computer for the next 2 days. I will try your solution on Monday and will surely let you know that did it work or not. I hope it does

Meanwhile If you guys could kindly let me know the reason behind changing the signs.
As of now cell H2 contains From date & I2 contains To date.

For Example
H2 which contains From date has 1-Jul-2019
I2 which contains To date has 22-Jul-2019

Now what I am telling the formula is to look at values which are greater than or equal to 1-Jul-2019 & less than or equal to 22-Jul-2019 i.e. 1-Jul To 22-Jul.

Now if I change the signs than won’t it be like I am telling the formula to look at values less than or equal to 1-Jul & greater than or equal to 22-Jul. In said case (to the best of my understanding) the formula will give me all the values which does not fall under this period i.e. 1-Jul To 22-Jul whereas, I want the all values which falls under these dates
i.e. 1-Jul To 22-Jul.

I just realised that one thing which I might have misunderstood (I guess) is that you guys are telling me to enter To date first & then From date. If that’s the case then I will be entering 22-Jul in H2 & 1-Jul in I2.

Hoping to hear from you guys.

Regards,

Humayun

Last edited:

#### DanteAmor

##### Well-known Member
The deduction is because you put this:

Cells H2 & I2 in the formula represents dates (From - To)[/QUOTE]

H2 is the minor date, and I2 is the major date.

H2 = 1-Jul-2019

I2 =
22-Jul-2019

For example: 15-Jul-2019

If
1-Jul-2019 <= 15-Jul-2019 True
if 22
-Jul-2019 >= 15-Jul-2019 True

In this case 15-Jul-2019 is between the 2 dates, then it is true

#### hrayani

##### Well-known Member
Thanks DanteAmor for the explanation

Now I get it.....

But in Sumif the logic does not work like this as far as I remember.
We put >= in the minor date & <= in the major date.

Am I right ?

Regards,

Humayun

#### DanteAmor

##### Well-known Member
The date to be evaluated must be greater than the FROM date and must be less than the TO date.

The structure of sumifs starts with "> =" but the principle is the same.

Let's see if the following examples are clearer:

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Date</td><td >Value</td><td > </td><td >From</td><td >To</td><td >Result</td><td >Result</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">15-jun</td><td style="text-align:right; ">120</td><td > </td><td style="text-align:right; ">01/07/2019</td><td style="text-align:right; ">21/07/2019</td><td style="text-align:right; ">170</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">30-jun</td><td style="text-align:right; ">145</td><td > </td><td > </td><td > </td><td style="text-align:right; ">170</td><td style="text-align:right; ">170</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">15-jul</td><td style="text-align:right; ">170</td><td > </td><td > </td><td > </td><td style="text-align:right; ">170</td><td style="text-align:right; ">170</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">30-jul</td><td style="text-align:right; ">195</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">14-ago</td><td style="text-align:right; ">220</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >F2</td><td >=SUMIFS(B2:B6,A2:A6,">="&D2,A2:A6,"<="&E2)</td></tr><tr><td >F3</td><td >=SUMPRODUCT((A2:A6>=D2)*(A2:A6<=E2)*(B2:B6))</td></tr><tr><td >G3</td><td >=SUMPRODUCT((D2<=A2:A6)*(E2>=A2:A6)*(B2:B6))</td></tr><tr><td >F4</td><td >{=SUM(IF((A2:A6>=D2)*(A2:A6<=E2),B2:B6))}</td></tr><tr><td >G4</td><td >{=SUM(IF((D2<=A2:A6)*(E2>=A2:A6),B2:B6))}</td></tr></table></td></tr></table>

#### hrayani

##### Well-known Member
Thanks DanteAmor once again for all the time you have put in to make me understand.

Rest I will try the solution provided in post # 2 & 3 provided by Marcelo & you.

I will keep you guys posted

#### DanteAmor

##### Well-known Member
Thanks DanteAmor once again for all the time you have put in to make me understand.

Rest I will try the solution provided in post # 2 & 3 provided by Marcelo & you.

I will keep you guys posted
Youre welcome. Let me know if you have any doubt.

#### hrayani

##### Well-known Member
Hi DanteAmor,

Just applied the formula you provided. Its working PERFECT.

Here is the final Formula in cell H4

Code:
``=IFERROR(SMALL(IF((gsm_pc_weight_ref_no=\$G\$2)*(gsm_pc_weight_design_color_name=\$J\$2)*(gsm_pc_weight_date>=\$H\$2)*(gsm_pc_weight_date<=\$I\$2),gsm_pc_weight_value,""),1),"")``

Changed This
Code:
``[COLOR=#333333](\$H\$2>=gsm_pc_weight_date)*(\$I\$2<=gsm_pc_weight_date)[/COLOR]``
To
Code:
``````[COLOR=#333333](gsm_pc_weight_date>=[/COLOR][COLOR=#333333]\$H\$2[/COLOR][COLOR=#333333])*(gsm_pc_weight_date<=[/COLOR][COLOR=#333333]\$I\$2[/COLOR][COLOR=#333333])
[/COLOR]``````

Instead of changing signs. But I do got the logic behind changing signs. Also Applied iferror in the beginning.

I also wanted to extract all the Nth Small unique values for the result. So in cell H5 I put this formula

Code:
``````=IFERROR(SMALL(IF([COLOR=#ff0000](gsm_pc_weight_value>H4)[/COLOR]*(gsm_pc_weight_ref_no=\$G\$2)*(gsm_pc_weight_design_color_name=\$J\$2)*(gsm_pc_weight_date>=\$H\$2)*(gsm_pc_weight_date<=\$I\$2),gsm_pc_weight_value,""),1),"")[COLOR=#333333]
[/COLOR]``````
Let me know if there is a better way of doing this. I mean the added criteria marked in red for the unique values.

Best Regards,

Humayun

Last edited:

1,081,622
Messages
5,360,079
Members
400,569
Latest member
tcormack

### This Week's Hot Topics

• VBA (Userform)
Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
• List box that changes fill color
Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
• Remove duplicates and retain one. Cross-linked cases
Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
• VBA Copy and Paste With Duplicates
Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
• Macro
is it possible for a macro to run if the active cell value is different to the value above it
• IF DATE and TIME
I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...