Help for Nested Conditionals (IF/AND/OR/SUMIF etc.)

ACAyeras

New Member
Joined
Sep 24, 2020
Messages
5
Office Version
  1. 2013
Platform
  1. Windows
Hello and Good Day!

I have an issue with writing a proper conditional formula in Excel, so please help me

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

A. If A is "Oranges"
(A1) On a certain client, $5 would be deducted from gross income once if his total order is below $50
(A2) Once the total orange purchases of a client breaches the $50 mark, additional charges will be deducted so that 10% of the total order is reduced

B. If A is "Apples", gross income should be multiplied to 0.8, regardless of the situation

C. If A1 is "Pears," regardless of the client,
(A1) If the total value gross income is below 500, it should be multiplied 0.8
(A2) Once its between 500 and 10,000, gross income must be multiplied to 0.9
(A3) Once it exceeds 10,000, gross income should be multiplied to 0.95

D. If no fruit is placed in a row, net income should be empty, too

I need a formula that operates with all of this.

Just think of it that Oranges rely on the amount of a certain client's total order, while Pears works for total orders of all clients.

And Oranges are the one that breaks my mind, since I don't know the right function to look for all the orders of a certain client if it meets the criteria, while applying to all possible clients

I know it's confusing, 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! :):):)
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

ACAyeras

New Member
Joined
Sep 24, 2020
Messages
5
Office Version
  1. 2013
Platform
  1. Windows
Manual Calculation
 

Attachments

  • Screenshot_20200924-231111.jpg
    Screenshot_20200924-231111.jpg
    250.8 KB · Views: 6

Forum statistics

Threads
1,141,929
Messages
5,709,398
Members
421,634
Latest member
Spicoli21

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
Top