Excel Small If Formula For Multiple Criterias

hrayani

Well-known Member
Joined
Jul 23, 2010
Messages
757
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
Joined
Aug 23, 2010
Messages
16,317
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
Joined
Dec 3, 2018
Messages
8,735
Office Version
2007
Platform
Windows
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
Joined
Jul 23, 2010
Messages
757
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
Joined
Dec 3, 2018
Messages
8,735
Office Version
2007
Platform
Windows
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
Joined
Jul 23, 2010
Messages
757
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
Joined
Dec 3, 2018
Messages
8,735
Office Version
2007
Platform
Windows
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
Joined
Jul 23, 2010
Messages
757
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
Joined
Dec 3, 2018
Messages
8,735
Office Version
2007
Platform
Windows
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
Joined
Jul 23, 2010
Messages
757
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:

Forum statistics

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

Some videos you may like

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