If statement missing logic

A

anum

New Member
Joined
Aug 14, 2012
Messages
44
Office Version
  1. 365
Hi everyone,

on this fine Sunday evening, I'm trying to sort the logic out for below. Annoyingly, there are several options in cells B3 & C3 so trying to cater to that also. I'm trying to tell the formula that if C5= Growth then show me the correct sales numbers per below and the choices in B3 & C3 and if it says without growth, I want the 2021 value until 2030 for the same choices in B3 & C3. Sounds simple enough but I'm flabbergasted!

please help!

thank you! :)

Sample Data 18.07.21.xlsx
ABCDEFGHIJKLMNOPQRST
1
2
3AllAll BrandsAllAllPlyGrowth
4BothWoodAllSandalWithout Growth
5Without GrowthPlasticAllErgo
6AllFrivo
7AllAll Brands
8WoodPly
9AllAll BrandsWoodSandal
10NPV 10 YearsWoodTotal Wood
11USD '000 FX@FBP202021202220232024202520262027202820292030PlasticErgo
1212345678910PlasticFrivo
13 Net Trade Sales FALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEPlasticTotal Plastic
14
15
16
172021202220232024202520262027202820292030
18Ply1002003005008001,3002,1003,4005,5008,900Wood
19Sandal2002504507001,1501,8503,0004,8507,85012,700Wood
20Ergo2502755258001,3252,1253,4505,5759,02514,600Plastic
21Frivo3003256259501,5752,5254,1006,62510,72517,350Plastic
228501,0501,9002,9504,8507,80012,65020,45033,10053,550
23
24
Sheet1
Cell Formulas
RangeFormula
A9:B9A9=B3
D13:M13D13=IF(C5="Growth",IF($C$3="All Brands",C22/1000,IF(LEFT($C$3,5)="Total",SUMIF(M18:M21,B3,C18:C21)/1000,IF($C$3="All Brands",C22/1000,SUMIF(M18:M21,B3,C18:C21)/1000))))
E18:L21E18=D18+C18
C22:L22C22=SUM(C18:C21)
Cells with Data Validation
CellAllowCriteria
C5List=$S$3:$S$4
B3List=$P$3:$P$5
C3List=OFFSET($R$3,MATCH($B$3,$Q$3:$Q$14,0),0,COUNTIF(Q2:Q14,$B$3),1)
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sort by date Sort by votes
Hi Anum,

Each IF has a TRUE and FALSE option so when nesting so deep it's easy to lose track and that's why you're seeing FALSE. That first IF never has a FALSE option so it displays false. You can prove that by inserting ,C5 before the last parentheses so if Without Growth is chosen it will actually display that cell.
Excel Formula: 
=IF(C5="Growth",IF($C$3="All Brands",C22/1000,IF(LEFT($C$3,5)="Total",SUMIF(M18:M21,B3,C18:C21)/1000,IF($C$3="All Brands",C22/1000,SUMIF(M18:M21,B3,C18:C21)/1000))),c5)

I think you may only need one IF but I'm having a hard time understanding "if C5= Growth then show me the correct sales numbers per below and the choices in B3 & C3 and if it says without growth, I want the 2021 value until 2030" as I see no values, only sales figures?

Once I understand that it can be incorporated but for now I've changed your Datalist setup and table to select a wildcard * when All is selected by VLOOKUP and replaced the IF with SUMIFS.

Please take a look at this and let me know how close it is to what you want.

Anum2.xlsx
ABCDEFGHIJKLMNOPQR
1
2
3AllAll BrandsAll*All Brands*
4BothWoodWoodPlyPly
5Without GrowthPlasticPlasticSandalSandal
6ErgoErgo
7FrivoFrivo
8
9AllAll Brands
10NPV 10 Years
11USD '000 FX@FBP202021202220232024202520262027202820292030
1212345678910
13 Net Trade Sales 8501050190029504850780012650204503310053550
14
15
16
172021202220232024202520262027202820292030
18Ply10020030050080013002100340055008900Wood
19Sandal2002504507001150185030004850785012700Wood
20Ergo2502755258001325212534505575902514600Plastic
21Frivo30032562595015752525410066251072517350Plastic
228501050190029504850780012650204503310053550
Toadstool
Cell Formulas
RangeFormula
A9:B9A9=B3
D13:M13D13=SUMIFS(C$18:C$21,$M$18:$M$21,VLOOKUP($B$3,$O$3:$P$5,2,0),$B$18:$B$21,VLOOKUP($C$3,$Q$3:$R$7,2,0))
E18:L21E18=D18+C18
C22:L22C22=SUM(C18:C21)
Cells with Data Validation
CellAllowCriteria
C5List=$S$3:$S$4
B3List=$O$3:$O$5
C3List=$Q$3:$Q$7
 
Upvote 0
Thank you! I think you steered me in the correct direction as where you've put C5 at the end of the formula, I've changed it to show the 2021 value for C3 so think I got it right.
If we take 2021 as an example, if they choose Wood is B3 & Total Wood in C3, then I want the Net Trade Sale for 2021 to show 300 which sums the Wood from col M, if they select all Brands then show 850 and if they select Sandal, 200 and so on...

Unfortunately I need the Total Wood and Total Plastic subset as my stakeholders need it which is missing in your new arrangement.
 
Upvote 0
I'm still not understanding the difference in calculation between "Growth" and "Without Growth".

"Unfortunately I need the Total Wood and Total Plastic subset as my stakeholders need it which is missing in your new arrangement."
...but that's the selection in B3, everything, all Wood or all Plastic?
 
Upvote 0
Growth is meant to show data from the table below as per the corresponding years and without growth will be the same value as 2021
 
Upvote 0
anum said:
Growth is meant to show data from the table below as per the corresponding years and without growth will be the same value as 2021
Click to expand...
So maybe that's where an IF statement comes in?

Anum2.xlsx
ABCDEFGHIJKLMNOPQR
1
2
3WoodAll BrandsAll*All Brands*
4BothWoodWoodPlyPly
5Without GrowthPlasticPlasticSandalSandal
6ErgoErgo
7FrivoFrivo
8
9WoodAll Brands
10NPV 10 Years
11USD '000 FX@FBP202021202220232024202520262027202820292030
1212345678910
13 Net Trade Sales 300300300300300300300300300300
14
15
16
172021202220232024202520262027202820292030
18Ply10020030050080013002100340055008900Wood
19Sandal2002504507001150185030004850785012700Wood
20Ergo2502755258001325212534505575902514600Plastic
21Frivo30032562595015752525410066251072517350Plastic
228501050190029504850780012650204503310053550
Toadstool2
Cell Formulas
RangeFormula
A9:B9A9=B3
D13D13=SUMIFS(C$18:C$21,$M$18:$M$21,VLOOKUP($B$3,$O$3:$P$5,2,0),$B$18:$B$21,VLOOKUP($C$3,$Q$3:$R$7,2,0))
E13:M13E13=IF($C$5="Without Growth",$D$13,SUMIFS(D$18:D$21,$M$18:$M$21,VLOOKUP($B$3,$O$3:$P$5,2,0),$B$18:$B$21,VLOOKUP($C$3,$Q$3:$R$7,2,0)))
E18:L21E18=D18+C18
C22:L22C22=SUM(C18:C21)
Cells with Data Validation
CellAllowCriteria
C5List=$S$3:$S$4
B3List=$O$3:$O$5
C3List=$Q$3:$Q$7
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

esset
  • Solved
Splitting hours worked into multiple shift patterns
Replies
3
Views
769
esset
esset
N
  • Question Question
Count - specific consecutive order
Replies
6
Views
984
Peter_SSs
Peter_SSs
F
  • Solved
Copy selected range to another worksheet, stop, and go to the next record to select (VBA)
Replies
3
Views
201
Alex Blakenburg
A
M
  • Solved
Alternative to nested IF Formula
2
Replies
11
Views
1K
Fluff
Fluff
M
  • Locked
  • Question Question
Alternative to nested If
Replies
1
Views
407
Joe4
Joe4

Forum statistics

Threads
1,223,227
Messages
6,170,849
Members
452,361
Latest member
d3ad3y3

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