Nested IFS/Multiple Formulas

[ncrb_10]

Hello,

I am seeking help on the following problem:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]
[/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD]d[/TD]
[TD]f[/TD]
[TD]g[/TD]
[TD]h[/TD]
[TD]i[/TD]
[TD]j[/TD]
[TD]k[/TD]
[TD]l[/TD]
[TD]m[/TD]
[TD]n[/TD]
[TD]o[/TD]
[TD]p[/TD]
[TD]q[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Upgrade 1[/TD]
[TD]Upgrade 2[/TD]
[TD]Upgrade 3[/TD]
[TD]# employees[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[TD]Sep[/TD]
[TD]Oct[/TD]
[TD]Nov[/TD]
[TD]Dec[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]3[/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]

I need to write a formula for cells F2:Q2 that calculate the following:

For example - in cell F2

If F1 = A2 then F2=D3 + If F1 = B2 then F2=D3 = If F1 = C3 then F2=D3

So, let's say Cells A2:C2 all contained "Jan", then cell F2 would equal "9". On the other hand, based on the current values of cells A2:C:2, cell a2=3, g2=3, and h2=3.

Let me know if I need to clarify as I am aware this may seem confusing.

Thanks!

 

[jtakw]

Hi,

Try this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Upgrade 1	Upgrade 2	Upgrade 3	# employees	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
2	Jan	Feb	Mar	3	3	3	3	0	0	0	0	0	0	0	0	0
Sheet597

Cell Formulas
Range	Formula
E2	=COUNTIF($A2:$C2,E1)*$D2

Formula copied across.

 

[ncrb_10]

Hi,

Try this:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
Upgrade 1	Upgrade 2	Upgrade 3	# employees	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
Jan	Feb	Mar

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

</tbody>

Sheet597​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]=COUNTIF($A2:$C2,E1)*$D2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Formula copied across.

That worked perfect, thanks! Now I have one more argument to add.

I want to add a new column in between C and D. In that Column is going to be the # of months it will take to complete a project. Let's say for example the # of months is 3. So the new formula in E2 needs to calculate:

If F1 = (A2-3) then F2=D3 + If F1 = (B2-3) then F2=D3 = If F1 = (C3-3) then F2=D3

How can the formula you provided be adjusted accordingly?

Thanks!

 

[jtakw]

Before we go further, based on your New setup, are the "month" values in F1:Q1 and A2:C2 Real Date values or Text ?

 

[ncrb_10]

Before we go further, based on your New setup, are the "month" values in F1:Q1 and A2:C2 Real Date values or Text ?

Real date values

 

[jtakw]

Not 100% certain I understand your requirement, do you mean something like this ?

F3 formula copied across:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Upgrade 1	Upgrade 2	Upgrade 3	# Months	# employees	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
2	Jan	Feb	Mar	3	3	3	3	3	0	0	0	0	0	0	0	0	0
3						0	0	0	3	3	3	0	0	0	0	0	0
Sheet597

Cell Formulas
Range	Formula
F2	=COUNTIF($A2:$C2,F1)*$E2
F3	=SUMPRODUCT((MONTH($A2:$C2)=MONTH(EOMONTH(F1,-$D2)))*$D2)

If it's not what you mean, please explain in detail and/or show a few samples with expected results.

 

[ncrb_10]

Not 100% certain I understand your requirement, do you mean something like this ?

F3 formula copied across:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
Upgrade 1	Upgrade 2	Upgrade 3	# Months	# employees

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]Jan[/TD]
[TD="align: right"]Feb[/TD]
[TD="align: right"]Mar[/TD]
[TD="align: right"]Apr[/TD]
[TD="align: right"]May[/TD]
[TD="align: right"]Jun[/TD]
[TD="align: right"]Jul[/TD]
[TD="align: right"]Aug[/TD]
[TD="align: right"]Sep[/TD]
[TD="align: right"]Oct[/TD]
[TD="align: right"]Nov[/TD]
[TD="align: right"]Dec[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]Jan[/TD]
[TD="align: right"]Feb[/TD]
[TD="align: right"]Mar[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

</tbody>

Sheet597​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=COUNTIF($A2:$C2,F1)*$E2[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F3[/TH]
[TD="align: left"]=SUMPRODUCT((MONTH($A2:$C2)=MONTH(EOMONTH(F1,-$D2)))*$D2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



If it's not what you mean, please explain in detail and/or show a few samples with expected results.

Let me try explaining the problem again.

Cells A2:C2 contain months in which an "upgrade" needs to be completed by. Cell D2 contains the number of months that the employees will need to complete the upgrade. Cell E2 contains the number of employees needed. I want to record the budgeted expense for the employees in the month that they will start working. i.e In cells F2:Q2, I want to calculated the following example:

If cell F2=(A2-D2) or F2=(B2-D2) or F2=(C2-D2), then F2=E2.

I realize now that A2:C2 should not be the first three months. Pretend that A2:C2 is Jun, Jul, Aug, respectively. In summary, based on the logic I am seeking, this table should show H2=3, I2=3, and J2=3.

Does that help clarify some of the confusion?
****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">
</body>

 

[jtakw]

Ok, if I understand correctly, just needed a slight adjustment to my formula, see if this does what you need, F3 formula copied across:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Upgrade 1	Upgrade 2	Upgrade 3	# Months	# employees	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
2	Jun	Jul	Aug	3	3	0	0	0	0	0	3	3	3	0	0	0	0
3						0	0	3	3	3	0	0	0	0	0	0	0
Sheet597

Cell Formulas
Range	Formula
F2	=COUNTIF($A2:$C2,F1)*$E2
F3	=SUMPRODUCT((MONTH($A2:$C2)=MONTH(EOMONTH(F1,$D2)))*$E2)

 

[ncrb_10]

Ok, if I understand correctly, just needed a slight adjustment to my formula, see if this does what you need, F3 formula copied across:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
Upgrade 1	Upgrade 2	Upgrade 3	# Months	# employees

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]Jan[/TD]
[TD="align: right"]Feb[/TD]
[TD="align: right"]Mar[/TD]
[TD="align: right"]Apr[/TD]
[TD="align: right"]May[/TD]
[TD="align: right"]Jun[/TD]
[TD="align: right"]Jul[/TD]
[TD="align: right"]Aug[/TD]
[TD="align: right"]Sep[/TD]
[TD="align: right"]Oct[/TD]
[TD="align: right"]Nov[/TD]
[TD="align: right"]Dec[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]Jun[/TD]
[TD="align: right"]Jul[/TD]
[TD="align: right"]Aug[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

</tbody>

Sheet597​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=COUNTIF($A2:$C2,F1)*$E2[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F3[/TH]
[TD="align: left"]=SUMPRODUCT((MONTH($A2:$C2)=MONTH(EOMONTH(F1,$D2)))*$E2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Wow... This is exactly what I needed.

Do you mind explaining the logic behind this formula string?

 

[ncrb_10]

Ok, if I understand correctly, just needed a slight adjustment to my formula, see if this does what you need, F3 formula copied across:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
Upgrade 1	Upgrade 2	Upgrade 3	# Months	# employees

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]Jan[/TD]
[TD="align: right"]Feb[/TD]
[TD="align: right"]Mar[/TD]
[TD="align: right"]Apr[/TD]
[TD="align: right"]May[/TD]
[TD="align: right"]Jun[/TD]
[TD="align: right"]Jul[/TD]
[TD="align: right"]Aug[/TD]
[TD="align: right"]Sep[/TD]
[TD="align: right"]Oct[/TD]
[TD="align: right"]Nov[/TD]
[TD="align: right"]Dec[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]Jun[/TD]
[TD="align: right"]Jul[/TD]
[TD="align: right"]Aug[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

</tbody>

Sheet597​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=COUNTIF($A2:$C2,F1)*$E2[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F3[/TH]
[TD="align: left"]=SUMPRODUCT((MONTH($A2:$C2)=MONTH(EOMONTH(F1,$D2)))*$E2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

I actually ran into a bit of an issue. For some reason, the formula is returning a "6" in F2. Any idea why?