additional criteria adding

[sachin483]

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

SS	DESG	ACH%	FROM	TO	AMT
AA	SS1	100	100000	158999	50000
AA	SS2	100	159000	217999	65000
AA	SS3	100	218000	276999	80000
AA	SS4	100	277000	9999999	95000
AB	SS1	100	120000	188999	65000
AB	SS2	100	189000	257999	90000
AB	SS3	100	258000	326999	115000
AB	SS4	100	327000	9999999	140000
AC	SS1	100	110000	186999	45000
AC	SS2	100	187000	263999	60000
AC	SS3	100	264000	340999	75000
AC	SS4	100	341000	9999999	90000

<tbody>
</tbody>

data:-

SS	DESG	ACH	RANGE	AMT REQUIRE
AA	SS1	100	105000	50000
AA	SS2	99	220000	0
AA	SS3	100	95000	0
AA	SS4	100	195000	65000
AB	SS1	100	180000	65000
AB	SS2	100	85000	0
AB	SS3	99	300000	0
AB	SS4	100	350000	115000
AC	SS1	100	101000	60000
AC	SS2	99	400000	0
AC	SS3	100	750000	90000
AC	SS4	100	225000	60000

<tbody>
</tbody>

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)

 

[vmjan02]

hi,
Just add ",$B$2:$B$13,N2,$C$2:$C$13,P2)" after L2. then the formula will work.

 

[sachin483]

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

AC

SS3

100

223000

9999999

75000

<tbody>
</tbody>

data

AC

SS3

110

750000

<tbody>
</tbody>

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)

 

[vmjan02]

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

75000

(Output)

AC(I12)

SS3(j12)

100(k12)

264025(L12)

<tbody>
</tbody>

It should work.

 

[sachin483]

100(k12) can be flexible like >= 100

 

[vmjan02]

=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

 

[sachin483]

$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

 

[vmjan02]

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

 

[sachin483]

thanks a lot working fine

 

[vmjan02]

enjoy