Excel Small If Formula For Multiple Criterias

[hrayani]

Hello Friends,

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

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

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]

Hello Friends,

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

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

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

 

[DanteAmor]

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]

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]

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]

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]

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]

Hi DanteAmor,

Just applied the formula you provided. Its working PERFECT.


Here is the final Formula in cell H4

=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

[COLOR=#333333]($H$2>=gsm_pc_weight_date)*($I$2<=gsm_pc_weight_date)[/COLOR]

To

[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

=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