HELP for IF STATEMENT, possibly with OR and AND Functions

I have a problem that my boss sent to me and I'm in a deep problem for three days already
I tried to solve this on my own and asked other forums but to no avail, and now, my boss wants the file back

Here's my issue:

Column A have either "Oranges", "Apples", or "Pears"
Column B have either "Fresh", "Moderate", or "Mature"
I'll input a number on C
Result will be flashed on D


In a row...

If A is "Oranges"
(a) If B is "Fresh",
(a1) If C is greater than or equal to 50, C should multiply to 0.9
(a2) If C is between 0 and 50, C should be subtracted by 5
(b) If B is "Moderate", the value of C is the same as the result in D1
(c) If B is "Mature", the value of C should also be multiplied to 0.9

If A is "Apples", it should multiply B to 0.8, regardless of B and C's values

If A1 is "Pears"
(a) Regardless of B's value
(a1) If the total value of Cs with "Pears" in their As (across the sheet) is below 500, C should be multiplied to 0.8
(a2) Once the total value of Cs with "Pears" in their As (across the sheet) is between 500 and 10,000, C should be multiplied to 0.9
(a3) Once the total value of Cs with "Pears" in their As (across the sheet) is greater than 10,000, then the C should be multiplied to 0.95

If A is empty, D should be empty, too


It's really confusing for me and I can't do anything about it!
Please help me and I would really appreciate it!

Thank You and God Bless to you all!


P.S. Tell me if you need the original file, but it's really chaotic and I almost did nothing about it

May I ask a few questions, Ma'am/Sir! I'm also trying to decipher the formula and learn it myself

1. I think there is no condition in the formula if the Oranges are Moderate? I checked the formula and tried to input the values, but it doesn't retain and copy the value of C to D

2. Why does the first condition of the formula is ","? Because I didn't encounter that before.


By the way, Thank You for your answer, Sir! I'm already close to submitting this.

I agree a sample sheet would be helpful and you can then check all of your outcomes are as you expect, perhaps tweaking bebos formula

=IF(A1="","",IF(A1="Oranges",IF(B1="Mature",C1*0.9,IF(B1="Moderate",C1,IF(C1<50,C1-5,C1*0.9))),IF(A1="Apples",C1*0.8,IF(A1="Pears",LOOKUP(SUMIF($A$1:$A$4,A1,$C$1:$C$4),{0,500,10001},{0.8,0.9,0.95})*C1))))

Thank You for your insights, everyone!
Your suggestions worked and I was able to submit my document to my boss!

All of you are my life savers! Once again, Thank You for your generosity and help!

Hello, Everyone!

It's humiliating, but I need your help yet again
There were some changes in the calculations and it now focuses on the client and not on the fruit quality

To elaborate:
Here's my issue:

Column A are Fruits; They either have "Oranges", "Apples", or "Pears"
Column B are Client's Names
I'll input the gross income on C
Result for net income will be flashed on D


In a row...

If A is "Oranges"
In a client, $5 would be deducted if the order is below $50, and it doesn't care whether it's the from a single order or multiple ones
Once the total orange purchases of a client breaches the $50 mark, 10% of the total orders from the beginning will be deducted

If A is "Apples", it should multiply B to 0.8, regardless of B and C's values

If A1 is "Pears"
(a) Regardless of B's value
(a1) If the total value of Cs with "Pears" in their As (across the sheet) is below 500, C should be multiplied to 0.8
(a2) Once the total value of Cs with "Pears" in their As (across the sheet) is between 500 and 10,000, C should be multiplied to 0.9
(a3) Once the total value of Cs with "Pears" in their As (across the sheet) is greater than 10,000, then the C should be multiplied to 0.95

If A is empty, D should be empty, too


I know it's confusing and I don't want to stir chaos like last time, so I'll attach a sample with my manual calculations and notes.
Please don't hesitate to tell me if you need some clarifications

Thank You everyone and God Bless!

why is e8 45 500 has not been passed?

in e2
=IF(AND(B2="oranges",COUNTIFS($B$1:B1,"oranges",$C$1:C1,C2 )=0,SUMIFS($D$1:D2,$B$1:B2,"oranges",$C$1:C2,C2 )<50),D2-5,IF(AND(B2="oranges",SUMIFS($D$1:D2,$B$1:B2,"oranges",$C$1:C2,C2 )<50),D2,IF(AND(B2="oranges",SUMIFS($D$1:D2,$B$1:B2,"oranges",$C$1:C2,C2 )>=50),D2-MIN(SUMIFS($D$1:D2,$B$1:B2,"oranges",$C$1:C2,C2 )-50,D2)*0.1,IF(B2="apples",D2*0.8,IF(B2="pears",D2*0.8+MEDIAN(0,SUMIFS($D$1:D2,$B$1:B2,"pears" )-500,D2)*0.1+MEDIAN(0,SUMIFS($D$1:D2,$B$1:B2,"pears")-10000,D2)*0.05,"")))))

Originally Posted by davsth

why is e8 45 500 has not been passed?

Because the total of gross income of pears already exceeded the 500 mark (Look at row 4, 7, and 8)

If Oranges depend on the total of every client, Pears depend on the total of all clients