additional criteria adding

S

sachin483

Board Regular
Joined
Mar 31, 2015
Messages
157
Office Version
  1. 2019
Platform
  1. Windows
I have grid as below and comparing column number A,D & E amount is arrived but now I want to add additional criteria column B & C

grid

[TABLE="width: 384"]
<tbody>[TR]
[TD="class: xl64, width: 64"]SS[/TD]
[TD="class: xl64, width: 64"]DESG[/TD]
[TD="class: xl64, width: 64"]ACH%[/TD]
[TD="class: xl63, width: 64"]FROM[/TD]
[TD="class: xl64, width: 64"]TO[/TD]
[TD="class: xl64, width: 64"]AMT[/TD]
[/TR]
[TR]
[TD="class: xl64"]AA[/TD]
[TD="class: xl64"]SS1[/TD]
[TD="class: xl64"]100[/TD]
[TD="class: xl63, align: right"]100000[/TD]
[TD="class: xl63, align: right"]158999[/TD]
[TD="class: xl63, align: right"]50000[/TD]
[/TR]
[TR]
[TD="class: xl64"]AA[/TD]
[TD="class: xl64"]SS2[/TD]
[TD="class: xl64"]100[/TD]
[TD="class: xl63, align: right"]159000[/TD]
[TD="class: xl63, align: right"]217999[/TD]
[TD="class: xl63, align: right"]65000[/TD]
[/TR]
[TR]
[TD="class: xl64"]AA[/TD]
[TD="class: xl64"]SS3[/TD]
[TD="class: xl64"]100[/TD]
[TD="class: xl63, align: right"]218000[/TD]
[TD="class: xl63, align: right"]276999[/TD]
[TD="class: xl63, align: right"]80000[/TD]
[/TR]
[TR]
[TD="class: xl64"]AA[/TD]
[TD="class: xl64"]SS4[/TD]
[TD="class: xl64"]100[/TD]
[TD="class: xl63, align: right"]277000[/TD]
[TD="class: xl63, align: right"]9999999[/TD]
[TD="class: xl63, align: right"]95000[/TD]
[/TR]
[TR]
[TD="class: xl64"]AB[/TD]
[TD="class: xl64"]SS1[/TD]
[TD="class: xl64"]100[/TD]
[TD="class: xl63, align: right"]120000[/TD]
[TD="class: xl63, align: right"]188999[/TD]
[TD="class: xl63, align: right"]65000[/TD]
[/TR]
[TR]
[TD="class: xl64"]AB[/TD]
[TD="class: xl64"]SS2[/TD]
[TD="class: xl64"]100[/TD]
[TD="class: xl63, align: right"]189000[/TD]
[TD="class: xl63, align: right"]257999[/TD]
[TD="class: xl63, align: right"]90000[/TD]
[/TR]
[TR]
[TD="class: xl64"]AB[/TD]
[TD="class: xl64"]SS3[/TD]
[TD="class: xl64"]100[/TD]
[TD="class: xl63, align: right"]258000[/TD]
[TD="class: xl63, align: right"]326999[/TD]
[TD="class: xl63, align: right"]115000[/TD]
[/TR]
[TR]
[TD="class: xl64"]AB[/TD]
[TD="class: xl64"]SS4[/TD]
[TD="class: xl64"]100[/TD]
[TD="class: xl63, align: right"]327000[/TD]
[TD="class: xl63, align: right"]9999999[/TD]
[TD="class: xl63, align: right"]140000[/TD]
[/TR]
[TR]
[TD="class: xl64"]AC[/TD]
[TD="class: xl64"]SS1[/TD]
[TD="class: xl64"]100[/TD]
[TD="class: xl63, align: right"]110000[/TD]
[TD="class: xl63, align: right"]186999[/TD]
[TD="class: xl63, align: right"]45000[/TD]
[/TR]
[TR]
[TD="class: xl64"]AC[/TD]
[TD="class: xl64"]SS2[/TD]
[TD="class: xl64"]100[/TD]
[TD="class: xl63, align: right"]187000[/TD]
[TD="class: xl63, align: right"]263999[/TD]
[TD="class: xl63, align: right"]60000[/TD]
[/TR]
[TR]
[TD="class: xl64"]AC[/TD]
[TD="class: xl64"]SS3[/TD]
[TD="class: xl64"]100[/TD]
[TD="class: xl63, align: right"]264000[/TD]
[TD="class: xl63, align: right"]340999[/TD]
[TD="class: xl63, align: right"]75000[/TD]
[/TR]
[TR]
[TD="class: xl64"]AC[/TD]
[TD="class: xl64"]SS4[/TD]
[TD="class: xl64"]100[/TD]
[TD="class: xl63, align: right"]341000[/TD]
[TD="class: xl63, align: right"]9999999[/TD]
[TD="class: xl63, align: right"]90000
[/TD]
[/TR]
</tbody>[/TABLE]

data:-

[TABLE="width: 348"]
<tbody>[TR]
[TD]SS[/TD]
[TD]DESG[/TD]
[TD]ACH[/TD]
[TD]RANGE[/TD]
[TD]AMT REQUIRE[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]SS1[/TD]
[TD]100[/TD]
[TD]105000[/TD]
[TD]50000[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]SS2[/TD]
[TD]99[/TD]
[TD]220000[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]SS3[/TD]
[TD]100[/TD]
[TD]95000[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]SS4[/TD]
[TD]100[/TD]
[TD]195000[/TD]
[TD]65000[/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]SS1[/TD]
[TD]100[/TD]
[TD]180000[/TD]
[TD]65000[/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]SS2[/TD]
[TD]100[/TD]
[TD]85000[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]SS3[/TD]
[TD]99[/TD]
[TD]300000[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]SS4[/TD]
[TD]100[/TD]
[TD]350000[/TD]
[TD]115000[/TD]
[/TR]
[TR]
[TD]AC[/TD]
[TD]SS1[/TD]
[TD]100[/TD]
[TD]101000[/TD]
[TD]60000[/TD]
[/TR]
[TR]
[TD]AC[/TD]
[TD]SS2[/TD]
[TD]99[/TD]
[TD]400000[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]AC[/TD]
[TD]SS3[/TD]
[TD]100[/TD]
[TD]750000[/TD]
[TD]90000[/TD]
[/TR]
[TR]
[TD]AC[/TD]
[TD]SS4[/TD]
[TD]100[/TD]
[TD]225000[/TD]
[TD]60000[/TD]
[/TR]
</tbody>[/TABLE]

the formula i am using

=SUMIFS($F$2:$F$13,$A$2:$A$13,I2,$D$2:$D$13,"<="&L2,$E$2:$E$13,">="&L2)
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sort by date Sort by votes
hi,
Just add ",$B$2:$B$13,N2,$C$2:$C$13,P2)" after L2. then the formula will work.
 
Upvote 0
the same is working but i want >=100 for formula P2 column means in P2 column there will be 100 or more ach% now reflecting zero as per below

grid

[TABLE="width: 384"]
<tbody>[TR]
[TD="class: xl64, width: 64"]AC[/TD]
[TD="class: xl64, width: 64"]SS3[/TD]
[TD="class: xl64, width: 64"]100[/TD]
[TD="class: xl63, width: 64, align: right"]223000[/TD]
[TD="class: xl63, width: 64, align: right"]9999999[/TD]
[TD="class: xl63, width: 64, align: right"]75000[/TD]
[/TR]
</tbody>[/TABLE]

data

[TABLE="width: 256"]
<tbody>[TR]
[TD="class: xl65, width: 64"]AC[/TD]
[TD="class: xl65, width: 64"]SS3[/TD]
[TD="class: xl65, width: 64"]110[/TD]
[TD="class: xl65, width: 64"]750000[/TD]
[/TR]
</tbody>[/TABLE]


formula:-
=SUMIFS($F$2:$F$12,$A$2:$A$12,I12,$D$2:$D$12,"<="&L12,$E$2:$E$12,">="&L12,$B$2:$B$12,J12,$C$2:$C$12,K12)
 
Upvote 0
As per the formula if I enter "AC" in I12, "SS3" in J12, "100" in K12 and anything from 264000 to 340999 in L12 I am getting the output as 75000

[TABLE="width: 384"]
<tbody>[TR]
[TD="width: 64, align: right"]75000[/TD]
[TD="width: 64"](Output)[/TD]
[TD="width: 64"] AC(I12)[/TD]
[TD="width: 64"]SS3(j12)[/TD]
[TD="width: 64, align: right"]100(k12)[/TD]
[TD="width: 64, align: right"]264025(L12)[/TD]
[/TR]
</tbody>[/TABLE]

It should work.
 
Last edited:
Upvote 0
=SUMIFS($F$2:$F$12,$A$2:$A$12,I12,$D$2:$D$12,"<="&L12,$E$2:$E$12,">="&L12,$B$2:$B$12,J12,$C$2:$C$12,">="&K12)

Changed formula
 
Upvote 0
$C$2:$C$12,">="&K12) -- if $C$2:$C$12, == 100 then k12 must be less than 100 to qualify the criteria as i understood it should be reverse, it is correct
 
Upvote 0
If k12 value will not be more than 100 then <= will work.

"<="&K12

and if K12 value will be more than 100 >= will work
">="&k12


 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Use 3 criteria to find piece of data
Replies
2
Views
764
jonathan92591
J

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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