Taking the right amount out of more than one condition

J

JimmyBambo

New Member
Joined
Dec 8, 2018
Messages
30
Hi friends,
I took picture of my problem as it is easier for me to explain on it, as I cant fully solve this problem.
So I have to determine percentage of discounts (F row) according to conditions in table on right side.
1) For different suppliers I have different discount rates on different dates,
2) For some products of suppliers I have also different discount rates.
So for example, if producer is Coca Cola, product is Coca Cola, date of invoice is 28.01.2018, discount is 5%. If it was product Fanta or Sprite, discount would be 7%.
I hope so that this is solvable, thank you in advance :beerchug:

jTyGvDN
V57Xzxg.png
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Sort by date Sort by votes
Thank you again.
If it was Fanta, discount would be 7% if it was invoiced until 30.04.2018.
If date of invoice is from 30.04.2018. until 25.08.2018., discount is 8% for fanta.-
For Sprite discount is 6% until 25.08.2018, from 25.08.2018 until 28.09.2018 is 7%.
If it was some product which is supplied by Coca Cola, but it is not in list "Product name" (column I), like Coca Cola zero for example, than take discount from name of supplier (Coca Cola, column H), and ignore column I "Product name".
 
Upvote 0
[TABLE="width: 1180"]
<colgroup><col span="3"><col><col span="9"><col span="3"><col span="2"></colgroup><tbody>[TR]
[TD]buyer[/TD]
[TD]supplier[/TD]
[TD]prod[/TD]
[TD]date[/TD]
[TD]dis[/TD]
[TD][/TD]
[TD]helper1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CS[/TD]
[TD]COC[/TD]
[TD]coca[/TD]
[TD="align: right"]28/01/2018[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD]COC[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CS[/TD]
[TD]COC[/TD]
[TD]fan[/TD]
[TD="align: right"]23/07/2018[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD]COCfan[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CS[/TD]
[TD]PEP[/TD]
[TD]pepsi[/TD]
[TD="align: right"]16/08/2018[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD]PEP[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CS[/TD]
[TD]NES[/TD]
[TD]nesc[/TD]
[TD="align: right"]21/11/2018[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD]NES[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]mytable[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FF[/TD]
[TD]PEP[/TD]
[TD]7up[/TD]
[TD="align: right"]05/02/2018[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD]PEP[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FF[/TD]
[TD]COC[/TD]
[TD]ccz[/TD]
[TD="align: right"]01/05/2018[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD]COC[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FF[/TD]
[TD]NES[/TD]
[TD]nesg[/TD]
[TD="align: right"]30/08/2018[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD]NESnesg[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]R[/TD]
[TD]PEP[/TD]
[TD]mir[/TD]
[TD="align: right"]15/03/2018[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD]PEPmir[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]helper[/TD]
[TD]supplier[/TD]
[TD]product[/TD]
[TD="align: right"]30/04/2018[/TD]
[TD="align: right"]25/08/2018[/TD]
[TD="align: right"]28/09/2018[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]R[/TD]
[TD]COC[/TD]
[TD]spr[/TD]
[TD="align: right"]30/06/2018[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD]COCspr[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]COC[/TD]
[TD]COC[/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]R[/TD]
[TD]COC[/TD]
[TD]coca[/TD]
[TD="align: right"]15/07/2018[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD]COC[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]COCfan[/TD]
[TD]COC[/TD]
[TD]fan[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]R[/TD]
[TD]NES[/TD]
[TD]nese[/TD]
[TD="align: right"]29/09/2018[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD]NES[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]COCspr[/TD]
[TD]COC[/TD]
[TD]spr[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]R[/TD]
[TD]NES[/TD]
[TD]nesa[/TD]
[TD="align: right"]31/10/2018[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD]NES[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]NES[/TD]
[TD]NES[/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]GS[/TD]
[TD]PEP[/TD]
[TD]spr[/TD]
[TD="align: right"]13/03/2018[/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD]PEPspr[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]NESnes[/TD]
[TD]NES[/TD]
[TD]nes[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]GS[/TD]
[TD]PEP[/TD]
[TD]mir[/TD]
[TD="align: right"]23/08/2018[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD]PEPmir[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]NESnesg[/TD]
[TD]NES[/TD]
[TD]nesg[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]GS[/TD]
[TD]NES[/TD]
[TD]neso[/TD]
[TD="align: right"]15/11/2018[/TD]
[TD="align: right"]12[/TD]
[TD][/TD]
[TD]NESneso[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]NESneso[/TD]
[TD]NES[/TD]
[TD]neso[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]12[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]PEP[/TD]
[TD]PEP[/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]11[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]PEPmir[/TD]
[TD]PEP[/TD]
[TD]mir[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]mytable2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01/01/2018[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01/05/2018[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]29/09/2018[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]31/12/2018[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]does this look along the right lines ?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
oldbrewer said:
can cocacola and pepsi both supply sprite ?
Click to expand...
I made mistake, there should be 7up. This is just example of my problem so I made some mistakes, because full table is something like 500k lines, but when I resolve this problem in example, I will resolve everything.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Find the matching details from 2 workbooks compile it in a sheet and then summarize it and put it in different sheet.
Replies
0
Views
388
deba2020
D
L
  • Solved
Fixed minimum rate for prorating calculation
3 4 5
Replies
41
Views
3K
KRice
K
P
  • Question Question
Multiple a dynamic Range based on conditions
Replies
0
Views
854
Pepperoni
P
N
  • Question Question
First in First Out (Fifo) Stock balance Calaculation File.
Replies
2
Views
10K
TotallyConfused
T
I
  • Question Question
Conditional formatting matching more than one criteria
Replies
0
Views
742
Ira Hopkins
I

Forum statistics

Threads
1,223,898
Messages
6,175,272
Members
452,628
Latest member
dd2

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