IF function with multiple logic?

RickyJH

New Member
Joined
Oct 28, 2019
Messages
4
Hi all,

I'm not sure if what I'm trying to do is possible, and if it is, how to go about it :confused:

I'm using Excel for a Sales Forecast - the data is extracted from a CRM and copied into my workbook - like the below table (first 3 columns only):

Est. Closing DateWeighted TotalActual Close DateSep-19Oct-19Nov-19Dec-19Jan-20Feb-20
Dec-1920Sep-1920
Oct-196Oct-196
Sep-1910Oct-1910
Sep-1912Sep-1912
Nov-1966

<tbody>
</tbody>



At the moment, I have an IF formula in each month column, that if the Estimated Closing Date is for example, Oct-10, then it will return the value from 'Weighted Total' in the Oct-19 column e.g. =IF(I5="Oct-19",L5,"").

Often if there is an Actual Close Date, then it's different to the Est. Close Date. So ideally I want the Est Closing Date formula as it is at the moment, UNLESS there's a month in the Actual Close Date, in which case return the Weighted Total to the Actual Close Date corresponding month.

So instead of the above table, it would look like the below:

Est. Closing DateWeighted TotalActual Close DateSep-19Oct-19Nov-19Dec-19Jan-20Feb-20
Dec-1920Sep-1920
Oct-196Oct-196
Sep-1910Oct-1910
Sep-1912Sep-1912
Nov-1966

<tbody>
</tbody>

The idea is to move away from using a manual spreadsheet where the team are inputting the details directly into Excel, and instead use our CRM to extract the relevant data.

Any help would be much appreciated - thank you!
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,791
Office Version
365
Platform
Windows
How about

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="font-weight: bold;;">Est. Closing Date</td><td style="font-weight: bold;;">Weighted Total</td><td style="font-weight: bold;;">Actual Close Date</td><td style="font-weight: bold;text-align: right;;">Sep-19</td><td style="font-weight: bold;text-align: right;;">Oct-19</td><td style="font-weight: bold;text-align: right;;">Nov-19</td><td style="font-weight: bold;text-align: right;;">Dec-19</td><td style="font-weight: bold;text-align: right;;">Jan-20</td><td style="font-weight: bold;text-align: right;;">Feb-20</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">Dec-19</td><td style="text-align: right;;">20</td><td style="text-align: right;;">Sep-19</td><td style="text-align: right;;">20</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">Oct-19</td><td style="text-align: right;;">6</td><td style="text-align: right;;">Oct-19</td><td style=";"></td><td style="text-align: right;;">6</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">Sep-19</td><td style="text-align: right;;">10</td><td style="text-align: right;;">Oct-19</td><td style=";"></td><td style="text-align: right;;">10</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">Sep-19</td><td style="text-align: right;;">12</td><td style="text-align: right;;">Sep-19</td><td style="text-align: right;;">12</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">Nov-19</td><td style="text-align: right;;">6</td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style="text-align: right;;">6</td><td style=";"></td><td style=";"></td><td style=";"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet3</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D2</th><td style="text-align:left">=IF(<font color="Blue">$C2<>"",IF(<font color="Red">$C2=D$1,$B2,""</font>),IF(<font color="Red">$A2=D$1,$B2,""</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

jmacleary

Well-known Member
Joined
Oct 5, 2015
Messages
942
Office Version
2007
Platform
Windows
Darn. I had almost the same forlmula but was struggling to post it. =IF(D$1=IF(ISBLANK($C2),$A2,$C2),$B2,"")
 
Last edited:

RickyJH

New Member
Joined
Oct 28, 2019
Messages
4
Thank you! I'm struggling to get it to work though (most likely my Monday morning brain!). The columns are set out as below - could you rewrite the formula to match the column positions please so I can check it again?!

ABCDEFGHIJKLLNOPQRSTUVWXYZ
1
2
3OPPJFLBDEUCCOMOPPNVALCATEst Order DatePWINRKWeighted TotalCMSTENActual Close DateSOApr-19May-19Jun-19Jul-19Aug-19Sep-19Oct-10Nov-19Dec-19
4
5May-195Sep-19
6Oct-1910Oct-19
7Nov-197Oct-19

<tbody>
</tbody>
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,791
Office Version
365
Platform
Windows
In R5 copied down & across
=IF($P5<>"",IF($P5=R$3,$L5,""),IF($I5=R$3,$L5,""))

But you have a typo in X3 it should be Oct-19
 
Last edited:

RickyJH

New Member
Joined
Oct 28, 2019
Messages
4
Great thank you. It's not showing any values though on either formula? I have no idea why...
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,791
Office Version
365
Platform
Windows
Are the dates actual dates formatted to show mmm-yy or are they text values?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,791
Office Version
365
Platform
Windows
In that case make sure they exactly match the headers, especially check for any leading/trailing spaces.
 

jmacleary

Well-known Member
Joined
Oct 5, 2015
Messages
942
Office Version
2007
Platform
Windows
And as the data is coming from an external crm source, check its not inserting any non-viewable characters into the strings.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,253
Messages
5,467,563
Members
406,543
Latest member
semoredhawk

This Week's Hot Topics

Top