Nested IFS/Multiple Formulas

N

ncrb_10

New Member
Joined
Feb 14, 2019
Messages
23
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!
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Sort by date Sort by votes
Hi,

Try this:


Book1
ABCDEFGHIJKLMNOP
1Upgrade 1Upgrade 2Upgrade 3# employeesJanFebMarAprMayJunJulAugSepOctNovDec
2JanFebMar3333000000000
Sheet597
Cell Formulas
RangeFormula
E2=COUNTIF($A2:$C2,E1)*$D2


Formula copied across.
 
Upvote 0
jtakw said:
Hi,

Try this:

ABCDEFGHIJKLMNOP
Upgrade 1Upgrade 2Upgrade 3# employeesJanFebMarAprMayJunJulAugSepOctNovDec
JanFebMar

<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.
Click to expand...

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!
 
Upvote 0
Before we go further, based on your New setup, are the "month" values in F1:Q1 and A2:C2 Real Date values or Text ?
 
Upvote 0
Not 100% certain I understand your requirement, do you mean something like this ?

F3 formula copied across:


Book1
ABCDEFGHIJKLMNOPQ
1Upgrade 1Upgrade 2Upgrade 3# Months# employeesJanFebMarAprMayJunJulAugSepOctNovDec
2JanFebMar33333000000000
3000333000000
Sheet597
Cell Formulas
RangeFormula
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.
 
Upvote 0
jtakw said:
Not 100% certain I understand your requirement, do you mean something like this ?

F3 formula copied across:

ABCDEFGHIJKLMNOPQ
Upgrade 1Upgrade 2Upgrade 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.
Click to expand...

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>
 
Upvote 0
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
ABCDEFGHIJKLMNOPQ
1Upgrade 1Upgrade 2Upgrade 3# Months# employeesJanFebMarAprMayJunJulAugSepOctNovDec
2JunJulAug33000003330000
3003330000000
Sheet597
Cell Formulas
RangeFormula
F2=COUNTIF($A2:$C2,F1)*$E2
F3=SUMPRODUCT((MONTH($A2:$C2)=MONTH(EOMONTH(F1,$D2)))*$E2)
 
Upvote 0
jtakw said:
Ok, if I understand correctly, just needed a slight adjustment to my formula, see if this does what you need, F3 formula copied across:

ABCDEFGHIJKLMNOPQ
Upgrade 1Upgrade 2Upgrade 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]
Click to expand...


Wow... This is exactly what I needed.

Do you mind explaining the logic behind this formula string?
 
Upvote 0
jtakw said:
Ok, if I understand correctly, just needed a slight adjustment to my formula, see if this does what you need, F3 formula copied across:

ABCDEFGHIJKLMNOPQ
Upgrade 1Upgrade 2Upgrade 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]
Click to expand...

I actually ran into a bit of an issue. For some reason, the formula is returning a "6" in F2. Any idea why?
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
AVERAGEIF TROUBLES
Replies
3
Views
79
AhoyNC
A
A
  • Solved
Select current month in combobox on userform for English language
2 3
Replies
29
Views
320
Ali M
A
G
  • Question Question
Copy some specific columns whose header is merge
2 3
Replies
26
Views
672
Joe4
Joe4
J
  • Question Question
auto filling cells based on preset format
Replies
1
Views
35
HighAndWilder
H
J
  • Question Question
adding cells based on criteria across several columns
Replies
5
Views
999
Cubist
Cubist

Forum statistics

Threads
1,224,816
Messages
6,181,138
Members
453,021
Latest member
Justyna P

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