If Formula using <0,Calculate %

[adityanshardul]

need If(B3:B7<0,D3:D7*10%) attached sample file for reference
A B C D
2 Add
3 10 100 100
4 0 100 100
5 5 100 100
6 0 100 100
7 0 100 100
8 500 500
9 Add 15 Result will be 20

 

[Somendra Misra]

Hi Aditya,

You file on Sheet3 there you want formula?
You want the sum of 10% of Column D value only if corresponding Column B value is less than 0?

Regards,

 

[adityanshardul]

Yes required Formula in Sheet 3
10% in each row if B is greater than 0

 

[Somendra Misra]

Put in E3 and copy down:

=IF(B3>0,0.1*D3,0) and than use a SUM in E9

Or if you want a single formula than use below
=SUMPRODUCT(($B$3:$B$8>0)*(0.1*$D$3:$D$8))

Regards,

 

[adityanshardul]

Great Done.
Thanks a lot
Can you Please help me in thread If Formula with Relation, Age & Discount

 

[Somendra Misra]

Will go through it.

Regards,

 

[adityanshardul]

=SUMPRODUCT((G14:G18>0)*(0.1*M14:M18))
This work great but small issue if i delete one content from my actual file which is formula base
error occured as #VALUE, Hence may require Formula with IFERROR !

 

[Somendra Misra]

In which range of the formula you are deleting?

Regards,

 

[adityanshardul]

If D3:D8 any of blank error occurred #VALUE

 

[adityanshardul]

Sending you Sample file but i dont want to make any change in D3 to D8 Formula, (Which is Correct & Result Shows me Blank (Array Formula)

 

[Somendra Misra]

Put an IFERROR around VLOOKUP.

Regards,

 

[adityanshardul]

SUMPRODUCT(($B$3:$B$8>0)*(0.1*$D$3:$D$8))
Want to use IFERROR in above formula is it possible

 

[Somendra Misra]

Try =SUMPRODUCT(IFERROR(($B$3:$B$8>0)*(0.1*$D$3:$D$8),0)) with Ctrl+Shift+Enter.

Regards,

 

[adityanshardul]

Yes Sir,Its Perfect

 

[adityanshardul]

Can you Please help me in thread If Formula with Relation, Age & Discount or else
attached sample file (Sheet 1) for your reference. J5 to J9 individually it is solved
want to make single Array Base formula in H13 (Half Solved)

 

[Somendra Misra]

I Had gone through your other thread, but a lil confused can you state all the conditions. OR is the table in the range N6:U12 are all the conditions?

Regards,

 

[adityanshardul]

I3 Tab = "Fresh" C5=Self F5 greater than 55,Cost <5000,0,20% & If "Quote"10%
I3 Tab = "Fresh" C7:C9=Self G5:G6<5000,child<3000,0,10%
I3 Tab = "Quote" C7:C9=Self G5:G6<5000,child<3000,0,10%

If you change I3 Tab & Few changes in Cost G5:G9 Such as 2000,3000,5000 result will be right in J5:j9
which i want to make in single formula H13

 

[Somendra Misra]

Will come back on this.

Regards,

 

[adityanshardul]

Tried this & Worked for this file But or actual file required IFERROR fomula

IF(AND($I$2="Source",G5<5000),IF(AND($I$3="Fresh",F5<55),H5*20%,H5*10%)*IF($G$11="Y",0.9,1),0)+IF(AND($I$2="Source",G6<5000),IF(AND($I$3="Fresh",F6<55),H6*20%,H6*10%)*IF($G$11="Y",0.9,1),0)+IF(AND(OR($G$5>=5000,$G$6>=5000),G7>=3000),0,H7*10%)*IF($G$11="Y",0.9,1)+IF(AND(OR($G$5>=5000,$G$6>=5000),G8>=3000),0,H8*10%)*IF($G$11="Y",0.9,1)+IF(AND(OR($G$5>=5000,$G$6>=5000),G9>=3000),0,H9*10%)*IF($G$11="Y",0.9,1)

 

[adityanshardul]

Hi again Stuck in if, Result require in J5
If I3="Fresh",If F5 is greater than 55, 10% to loaded rest formula is ok

 

[Somendra Misra]

Use =IF(AND($I$2="Source",G5<5000),IF(AND($I$3="Fresh",F5>55),H5*10%)*IF($G$11="Y",0.9,1),0)

Regards,

 

[adityanshardul]

Result shows 0, it should be 9

 

[Somendra Misra]

Check the condition of G5<5000 where as G5 = 5000 so FALSE and hence 0 from IF.

Regards,

 

[adityanshardul]

Sorry was confused
IF(AND($I$2="Source",G5<5000),IF(AND($I$3="Fresh",F5<55),H5*10%)*IF($G$11="Y",0.9,1),0)

result require 0 in case of "Fresh",<55 & cost greater than 1000