Conditional Formula

[ultracyclist]

I want to create a formula that I can carry down from rows F2:F83 on Sheet1 that has the following logic

IF C2:C83 = MS355 in Sheet1, multiply the quantity in E2:E83 (Sheet1) times the cost from sheet2 cell B4 = 450
IF C2:C83 = MS390 in Sheet1, multiply the quantity in E2:E83 (Sheet1) times the cost from sheet2 cell B7 = 700

Ex: C2 has MS355 in Sheet 1 and the quantity in E2 =12. The value returned in F2 should =$5400
C2 has MS390 in Sheet 1 and the quantity in E2 =20. The value returned in F2 should =$14000

Not sure where to start with this one. Any help would be appreciated

Thanks
Tom

 

[bebo021999]

Is MS355 cell address or text string?
May be:
1) text string:
F2:
=((C2="MS355")*sheet2!$B$4+(C2="MS390")*sheet2!$B$7)*E2

2) address
=((C2=$MS$355)*sheet2!$B$4+(C2=$MS$390)*sheet2!$B$7)*E2

Drag F2 down

 

[ultracyclist]

Is MS355 cell address or text string?
May be:
1) text string:
F2:
=((C2="MS355")*sheet2!$B$4+(C2="MS390")*sheet2!$B$7)*E2

2) address
=((C2=$MS$355)*sheet2!$B$4+(C2=$MS$390)*sheet2!$B$7)*E2

Drag F2 down

MS355 and MS390 are text string.

To clarify the quantities are also in Sheet1 in cells E2:E83. The formula shows *sheet2

 

[Dazzybeeguy]

Does this work?
=IFS(C2="MS355",E2*Sheet2!$B$4,C2="MS390",E2*Sheet2!$B$7)

 

[ultracyclist]

Does this work?
=IFS(C2="MS355",E2*Sheet2!$B$4,C2="MS390",E2*Sheet2!$B$7)

Let me give this formula a try in the next hour or sooner and I will reply back to this thread.

 

[bebo021999]

With G1 = 2018
(could be formatted as 0000" pmnt"
Try:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	COMP	DESCRIPTION (YR / MAKE / MODEL)	FINANCING	Mthly Lease Payment	Lease Start	Lease End	2018	2019	2020	2021	2022	2023	2024	2025	2026	2027	2028	2029	2030
2	QH	1 Titan5 Utility Box 10' x 12' L C/W Stacker & Accessories	Add Capital	799.68	01-Sep-2019	01-Aug-2024	0.00	3,198.72	9,596.16	9,596.16	9,596.16	9,596.16	6,397.44	0.00	0.00	0.00	0.00	0.00	0.00
3	QH	Gravel Screener	Add Capital	2145.92	01-Apr-2021	01-Mar-2027	0.00	0.00	0.00	19,313.28	25,751.04	25,751.04	25,751.04	25,751.04	25,751.04	6,437.76	0.00	0.00	0.00
4	KEL	JD - 2012 624K Loader	CWB	2737.23	01-May-2019	01-Apr-2026	0.00	21,897.84	32,846.76	32,846.76	32,846.76	32,846.76	32,846.76	32,846.76	10,948.92	0.00	0.00	0.00	0.00
5	AMP	2005 JD 8120	Brandt Finance	1787.52	01-May-2021	30-Apr-2027	0.00	0.00	0.00	14,300.16	21,450.24	21,450.24	21,450.24	21,450.24	21,450.24	7,150.08	0.00	0.00	0.00
6	KEL	2008 John Deere 750J Crawler Dozer	Brandt Finance	4934.59	24-Jun-2020	24-May-2024	0.00	0.00	34,542.13	59,215.08	59,215.08	59,215.08	24,672.95	0.00	0.00	0.00	0.00	0.00	0.00
7	WLS	JD - 2018 250G Excavator	John Deere	4645.61	31-Oct-2018	30-Sep-2024	13,936.83	55,747.32	55,747.32	55,747.32	55,747.32	55,747.32	41,810.49	0.00	0.00	0.00	0.00	0.00	0.00
8	WFP	332G Skidsteer	John Deere	1419.84	28-Oct-2019	28-Sep-2024	0.00	4,259.52	17,038.08	17,038.08	17,038.08	17,038.08	12,778.56	0.00	0.00	0.00	0.00	0.00	0.00
9	WLS	2018 John Deere 380GLC Excavator Base	Brandt Finance	5140.84	01-Sep-2020	01-Aug-2026	0.00	0.00	20,563.36	61,690.08	61,690.08	61,690.08	61,690.08	61,690.08	41,126.72	0.00	0.00	0.00	0.00
Sheet1

Cell Formulas
Range	Formula
H1:S1	H1	=G1+1
G2:S9	G2	=$D2*((YEAR($E2)=G$1)*(12-MONTH($E2)+1)+(YEAR($F2)=G$1)*MONTH($F2)+AND(G$1>YEAR($E2),G$1<YEAR($F2))*12)

 

[Dazzybeeguy]

Let me give this formula a try in the next hour or sooner and I will reply back to this thread.

Is there a scenario where C2:C83 is neither MS355 or MS390 ? if that's the case you would need a formula that took that into account, so the 1st IF formula would leave it Blank, alternatively change the 2nd option would multiply by whatever you state

=IF(C2="MS355",E2*Sheet2!$B$4,IF(C2="MS390",E2*Sheet2!$B$7,""))
=IF(C2="MS355",E2*Sheet2!$B$4,IF(C2="MS390",E2*Sheet2!$B$7,E2*Sheet2!$B$4))

 

[ultracyclist]

Let me give this formula a try in the next hour or sooner and I will reply back to this thread.

That worked perfectly. Thank you for the

Does this work?
=IFS(C2="MS355",E2*Sheet2!$B$4,C2="MS390",E2*Sheet2!$B$7)

This worked perfectly. Thank you very much for your assistance.

 

[ultracyclist]

Is MS355 cell address or text string?
May be:
1) text string:
F2:
=((C2="MS355")*sheet2!$B$4+(C2="MS390")*sheet2!$B$7)*E2

2) address
=((C2=$MS$355)*sheet2!$B$4+(C2=$MS$390)*sheet2!$B$7)*E2

Drag F2 down

Thank you for your help. This got me started.