Spreadsheet: 696749d1600960141-nested-if-function-orderboek-en-salesplanning-question-excel-forum
Sheet: Output salespipeline 

 | jan | feb | mrt | apr | mei | jun | jul | aug | sep | okt | nov | dec | totaal
[NAME] |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Rijlabels | sep-17 | aug-17
SaleS Orderboek |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Orderboek | 158449.2610063325 | 164771.4276729992
Totaal |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Salespipeline | 91475.20000066 | 68205.43713064713
Input sheet |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Eindtotaal | 249924.4610069925 | 232976.8648036463
Check
BV | 2018-01-01 00:00:00 | 2018-02-01 00:00:00 | 2018-03-01 00:00:00 | 2018-04-01 00:00:00 | 2018-05-01 00:00:00 | 2018-06-01 00:00:00 | 2018-07-01 00:00:00 | 2018-08-01 00:00:00 | 2018-09-01 00:00:00 | 2018-10-01 00:00:00 | 2018-11-01 00:00:00 | 2018-12-01 00:00:00 | totaal
Services
Hosting |  |  |  |  |  | 0 | 0 | 0 | 0 | 0 | 0 | 0
License
Maintenance |  |  |  |  |  | 0 | 0 | 0 | 0 | 0 | 0 | 0 |  |  |  |  |  |  | [NAME] VERPLAATSEN
GmbH
Services |  |  |  |  |  | 0 | 0 | 0 | 0 | 0 | 0 | 0
Hosting |  |  |  |  |  | 0 | 0 | 0 | 0 | 0 | 0 | 0
License |  |  |  |  |  | 0 | 0 | 0 | 0 | 0 | 0 | 0
Maintenance |  |  |  |  |  | 0 | 0 | 0 | 0 | 0 | 0 | 0
Reiskosten |  |  |  |  |  | 0 | 0 | 0 | 0 | 0 | 0 | 0
SarL
Services
Hosting |  |  |  |  |  | 0 | 0 | 0 | 0 | 0 | 0 | 0
License
Maintenance |  |  |  |  |  | 0 | 0 | 0 | 0 | 0 | 0 | 0
Eindtotaal
 | Salespipeline
Grand Total
Rijlabels | Q4 2017
Client interested
Offer sent
Oral Agreement
Eindtotaal
#REF!
 |  |  |  |  |  | 9 | 9 | 9 | 9
 |  |  |  |  | uren project
 | 286 |  |  |  | niet ure
 |  |  |  |  |  | 9 | 9 | 9 | 9
Q3 2018
 | Columns
Rows | Values
#REF!
 |  |  | BCI | 206394.6666666667
 |  |  | Nog te verwachten known leads | 160000
 |  |  | ITRI | 151131.6666666667
 |  |  | Goodweave | 126393.0666666667
 |  |  | RFA | 106350
 |  |  | TE/GOTS | 99750
 |  |  | SecureFeed | 92714.66666666667
 |  |  | Nog te verwachten unknown leads | 79200
 |  |  | Avined | 74922.66666666667
 |  |  | SDC (Swiss Agency for Development and Cooperation) Green Gold | 72270
0.1 | Aid by Trade Foundation (CmiA) | 0 | 0 | 0
0.1 | [NAME] (Australia maybe) | 0 | 800 | 800
0.1 | BMBF | 0 | 0 | 0
0.1 | Cargill | 0 | 0 | 714.2857142857143
0.1 | GlobalGAP | 0 | 0 | 0
0.1 | H&M | 0 | 0 | 500
0.1 | Pepsico | 2222.222222222222 | 2222.222222222222 | 2222.222222222222
0.1 | RainForest Alliance | 1000 | 1000 | 1000
0.1 | Supima | 0 | 4000 | 4000
0.1 | VFC (Timberland/ North Face) | 0 | 770 | 770
0.15 | Flocert | 0 | 0 | 857.1428571428571
0.2 | Biokap | 0 | 0 | 400
0.2 | Bionext | 0 | 0 | 2300
0.2 | Commoditybasis | 4520 | 4520 | 4520
0.2 | Gemüsering | 0 | 24.9975 | 24.9975
0.2 | Global 2000 | 430.5555555555555 | 430.5555555555555 | 430.5555555555555
0.2 | idh | 3100 | 3100 | 100
0.2 | Proterra | 0 | 0 | 0
0.2 | Responsible Jewellery Council RJC | 0 | 0 | 2000
0.2 | Storck | 2466.666666666667 | 2466.666666666667 | 2466.666666666667
0.2 | Unilever | 0 | 10000 | 10000
0.3 | Goodweave | 0 | 10498.5 | 10498.5
0.3 | Ortovox | 0 | 1950 | 1950
0.3 | Textile Exchange | 8347.5 | 8347.5 | 8347.5
0.4 | BCI | 0 | 0 | 0
0.5 | Nog te verwachten GmbH | 0 | 0 | 0
0.5 | Proterra | 12500 | 12500 | 0
0.5 | RainForest Alliance | 4575 | 4575 | 4575
0.7 | BCI | 0 | 0 | 0
0.8 | RainForest Alliance | 10000 | 10000 | 10000
0.9 | [NAME] | 0 | 0 | 0
0.95 | ITRI | 11843.33333333333 | 11843.33333333333 | 11843.33333333333
 |  | 61005.27777777778 | 89048.77527777778 | 80320.20384920634
0.1 | Beraca | 0 | 0 | 0
0.2 | Danone | 0 | 0 | 0
0.2 | L'Occitane | 0 | 0 | 0
0.2 | Merck | 0 | 0 | 0
0.2 | NRSC | 0 | 0 | 0
0.2 | Puratos | 0 | 0 | 0
0.2 | Transitions | 0 | 0 | 0
0.6 | Danone | 0 | 0 | 6000
 |  | 0 | 0 | 6000
0.05 | TFT | 0 | 0 | 0
0.1 | Avined | 0 | 0 | 0
0.2 | GMP+ | 0 | 0 | 0
0.2 | Nog te verwachten BV | 0 | 0 | 0
0.5 | Nog te verwachten BV | 2916.666666666667 | 2916.666666666667 | 2916.666666666667
0.5 | Nog te verwachten GmbH | 361.1111111111111 | 361.1111111111111 | 361.1111111111111
0.5 | Nog te verwachten [NAME] | 0 | 0 | 0
0.5 |  | 3277.777777777777 | 3277.777777777777 | 3277.777777777777
0 |  | 0 | 0 | 0
0.1 | Fleury Michon | 0 | 0 | 0
0.1 | FTA (Foreign Trade Assoc) / BSCI | 0 | 0 | 0
0.1 | MSD Animal Health | 111.1111111111111 | 111.1111111111111 | 111.1111111111111
0.2 | MSD UK | 200 | 200 | 200
0.8 | Mydibel | 0 | 0 | 666.6666666666666
 |  | 311.1111111111111 | 311.1111111111111 | 977.7777777777777
0 |  | 0 | 0 | 0
 |  | 0 | 0 | 0
0 |  | 0 | 0 | 0
0.1 | [NAME] du Nord | 411.1111111111111 | 411.1111111111111 | 411.1111111111111
0.1 | Supply chain master | 0 | 0 | 400
0.2 | Coeur de Forêt | 0 | 0 | 0
0.2 | IFTH | 102.5641025641026 | 102.5641025641026 | 102.5641025641026
0.2 | Perlim | 0 | 0 | 266.6666666666667
0.2 | Premiere Vision | 0 | 0 | 0
0.2 | [NAME] | 0 | 0 | 0
0.2 | Qualimat | 0 | 0 | 0
0.2 | Uneal | 2000 | 0 | 285.7142857142857
0.2 | [NAME] | 0 | 0 | 0
0.2 | Yamana | 0 | 0 | 0
0.3 | Cemoi | 0 | 1500 | 1500
0.5 | Nog te verwachten [NAME] | 0 | 0 | 0
 |  | 2513.675213675214 | 2013.675213675214 | 2966.056166056166
 |  | 67107.84188034188 | 94651.33938034189 | 93541.81557081809
#REF! |  |  | SalesPlanning Orderboek | Salespipeline
 |  | Aid by Trade Foundation (CmiA)
 |  | AIDA–Kripb-S: SMART Service Allianzen für Krisenprävention u. –bewältigung in Lebensmittelketten | Customer
 |  | Avined | Bionext
 |  | BCI | [NAME]
 |  | [NAME] | Firmenich
 |  | Beraca | Goodweave
 |  | Biokap | IFF
 |  | Bionext | ITA/ITRI
 |  | BMBF | MSC
 |  | Cemoi | Mydibel
 |  | Coeur de Forêt | Nog te verwachten known leads
 |  | Commoditybasis | Nog te verwachten unknown leads
 |  | Danone | Porphyrio
 |  | Eram | RainForest Alliance
 |  | Flocert | Responsible Jewellery Council RJC
 |  | FTA (Foreign Trade Assoc) / BSCI | SDC (Swiss Agency for Development and Cooperation) Green Gold
 |  | Gemüsering | Social accountability International
 |  | Global 2000 | Sustainable Electronics
 |  | GlobalGAP | TE/GOTS
 |  | GMP+ | TFT/NEXT
 |  | H&M | [NAME]
 |  | idh | Grand Total
 |  | IFTH
 |  | ITRI
 |  | [NAME]
 |  | L'Occitane
 |  | Merck
 |  | MSD Animal Health
 |  | MSD UK
 |  | Mydibel
 |  | n.a
 |  | Nog te verwachten BV
 |  | Nog te verwachten GmbH
 |  | Nog te verwachten [NAME]
 |  | NRSC
 |  | Ortovox
 |  | [NAME] du Nord
 |  | Perlim
 |  | Premiere Vision
 |  | [NAME]
 |  | Proterra
 |  | Puratos
 |  | Qualimat
 |  | RainForest Alliance
 |  | Responsible Jewellery Council RJC
 |  | Supima
 |  | Supply chain master
 |  | Technoserve
 |  | Textile Exchange
 |  | TFT
 |  | Transitions
 |  | Uneal
 |  | Unilever
 |  | VFC (Timberland/ North Face)
 |  | [NAME]
 |  | Yamana
 |  |  | Textile Exchange | 57390
VFC (Timberland/ North Face) | 1155 |  | ITRI | 24244
Avined | 1200 |  | RainForest Alliance | 22487.5
MSD UK | 1400 |  | [NAME] | 20930
BMBF | 1712.432432432433 |  | Commoditybasis | 17600
Supply chain master | 2000
Bionext | 2300
Biokap | 2400
Nog te verwachten GmbH | 2500
Beraca | 3000
Transitions | 3000
Perlim | 3166.666666666667
[NAME] du Nord | 3700
Global 2000 | 3874.999999999999
[NAME] | 4000
Aid by Trade Foundation (CmiA) | 4000
Coeur de Forêt | 4000
GMP+ | 4000
L'Occitane | 4000
NRSC | 4000
Uneal | 4000.000000000001
Merck | 5714.285714285715
idh | 6700
Eram | 7250
Nog te verwachten [NAME] | 7500
Supima | 8000
Technoserve | 8210
FTA (Foreign Trade Assoc) / BSCI | 10000
Puratos | 10000
GlobalGAP | 10850
Flocert | 11000
Qualimat | 11833.33333333333
Yamana | 12000
H&M | 13000
BCI | 14000
Cemoi | 15000
Ortovox | 15600
Danone | 17000
RainForest Alliance | 17862.5
Commoditybasis | 25600
ITRI | 30305
Nog te verwachten BV | 31500.00000000001
Mydibel | 32301.86666666666
[NAME] | 38575
Unilever | 40000
Textile Exchange | 57390
[NAME] | 58056.25
okt-17 | nov-17 | dec-17
 |  |  |  |  |  |  | orderintake totaal | q3 2017 | q4 2017 | 2018
 |  |  |  |  |  |  |  | 0 | 0
SalesPlanning Orderboek | Salespipeline
starting date work2 | starting date work3 | Starting Date Work | Expected
2018 | Qtr3 | jul | 3850
 |  | aug | 141068
 |  | sep | 275275
 | Qtr4 | okt | 379550 |  |  |  | Q2 2017 | Q3 2017 | Q4 2017
 |  | nov | 74500 |  |  |  | 21238.34268620268 | 24205.02805860805 | 41811.69472527473
 |  | dec | 25000 |  |  |  | 0 | 69356 | 124989
2019 | Qtr1 | jan | 287300 |  |  |  | 0 | 86394.87 | 57596.58
 | Qtr3 | jul | 60000 |  |  |  | 0 | 30170.03861003861 | 14056.67953667954
Grand Total |  |  | 1246543 |  |  |  | 0 | 104792.5 | 0
 |  |  |  |  |  |  | 0 | 1833.333333333333 | 0
 |  |  |  |  |  |  | 0 | 750 | 0
Q3 2018
SalesPlanning Orderboek | Salespipeline
Starting Date Work | Amount
jan | 395600
jul | 127700
aug | 300160
sep | 573250
okt | 769100
nov | 130000
dec | 50000
Grand Total | 2345810
 | [NAME] | augustus | september | oktober | november | december
jul | 41391.14713064713 | 39391.14713064713 | 33391.14713064713 | 27071.14713064714 | 22671.14713064713 | 15393.14713064713
aug | 0 | 28814.29 | 28814.29 | 26798.29 | 26798.29 | 26798.29
sep | 0 | 0 | 29269.76287001287 | 29269.76287001287 | 23769.76287001287 | 21436.42953667953
okt | 0 | 0 | 0 | 30280.83166666667 | 30280.83166666667 | 30280.83166666667
dec | 0 | 0 | 0 | 0 | 0 | 375
totaal
 |  | kans* waarde | [NAME]
 | jul | 179308.8827838828 | 958025
 | aug | 138023.45 | 864494
 | sep | 103745.7181467181 | 624600
 | okt | 90842.49500000001 | 771349.9
 | dec | 375 | 15000
okt-17 | nov-17 | dec-17
mei