# Nested IFS/Multiple Formulas

#### ncrb_10

##### New Member
Hello,

I am seeking help on the following problem:

 a b c d f g h i j k l m n o p q 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

<tbody>
</tbody>

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

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Hi,

Try this:

Book1
ABCDEFGHIJKLMNOP
2JanFebMar3333000000000
Sheet597
Cell Formulas
RangeFormula
E2=COUNTIF(\$A2:\$C2,E1)*\$D2

Formula copied across.

Hi,

Try this:

ABCDEFGHIJKLMNOP
2JanFebMar3333000000000

</tbody>
Sheet597

Worksheet Formulas
CellFormula
E2=COUNTIF(\$A2:\$C2,E1)*\$D2

</tbody>

<tbody>
</tbody>

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!

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

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

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

F3 formula copied across:

Book1
ABCDEFGHIJKLMNOPQ
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.

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

F3 formula copied across:

ABCDEFGHIJKLMNOPQ
2JanFebMar33333000000000
3000333000000

</tbody>
Sheet597

Worksheet Formulas
CellFormula
F2=COUNTIF(\$A2:\$C2,F1)*\$E2
F3=SUMPRODUCT((MONTH(\$A2:\$C2)=MONTH(EOMONTH(F1,-\$D2)))*\$D2)

</tbody>

<tbody>
</tbody>

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>

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
2JunJulAug33000003330000
3003330000000
Sheet597
Cell Formulas
RangeFormula
F2=COUNTIF(\$A2:\$C2,F1)*\$E2
F3=SUMPRODUCT((MONTH(\$A2:\$C2)=MONTH(EOMONTH(F1,\$D2)))*\$E2)

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
2JunJulAug33000003330000
3003330000000

</tbody>
Sheet597

Worksheet Formulas
CellFormula
F2=COUNTIF(\$A2:\$C2,F1)*\$E2
F3=SUMPRODUCT((MONTH(\$A2:\$C2)=MONTH(EOMONTH(F1,\$D2)))*\$E2)

</tbody>

<tbody>
</tbody>

Wow... This is exactly what I needed.

Do you mind explaining the logic behind this formula string?

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
2JunJulAug33000003330000
3003330000000

</tbody>
Sheet597

Worksheet Formulas
CellFormula
F2=COUNTIF(\$A2:\$C2,F1)*\$E2
F3=SUMPRODUCT((MONTH(\$A2:\$C2)=MONTH(EOMONTH(F1,\$D2)))*\$E2)

</tbody>

<tbody>
</tbody>

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

Replies
0
Views
194
Replies
20
Views
612
Replies
5
Views
172
Replies
7
Views
235
Replies
10
Views
285

1,203,323
Messages
6,054,724
Members
444,747
Latest member
Jaborsum

### 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.

### Which adblocker are you using?

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

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