IF function with multiple logic?

[RickyJH]

Hi all,

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

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):

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Est. Closing Date[/TD]
[TD]Weighted Total[/TD]
[TD]Actual Close Date[/TD]
[TD]Sep-19[/TD]
[TD]Oct-19[/TD]
[TD]Nov-19[/TD]
[TD]Dec-19[/TD]
[TD]Jan-20[/TD]
[TD]Feb-20[/TD]
[/TR]
[TR]
[TD]Dec-19[/TD]
[TD]20[/TD]
[TD]Sep-19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Oct-19[/TD]
[TD]6[/TD]
[TD]Oct-19[/TD]
[TD][/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sep-19[/TD]
[TD]10[/TD]
[TD]Oct-19[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sep-19[/TD]
[TD]12[/TD]
[TD]Sep-19[/TD]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Nov-19[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Est. Closing Date[/TD]
[TD]Weighted Total[/TD]
[TD]Actual Close Date[/TD]
[TD]Sep-19[/TD]
[TD]Oct-19[/TD]
[TD]Nov-19[/TD]
[TD]Dec-19[/TD]
[TD]Jan-20[/TD]
[TD]Feb-20[/TD]
[/TR]
[TR]
[TD]Dec-19[/TD]
[TD]20[/TD]
[TD]Sep-19[/TD]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Oct-19[/TD]
[TD]6[/TD]
[TD]Oct-19[/TD]
[TD][/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sep-19[/TD]
[TD]10[/TD]
[TD]Oct-19[/TD]
[TD][/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sep-19[/TD]
[TD]12[/TD]
[TD]Sep-19[/TD]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Nov-19[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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!

 

[Fluff]

How about

Book1
	A	B	C	D	E	F	G	H	I
1	Est. Closing Date	Weighted Total	Actual Close Date	Sep-19	Oct-19	Nov-19	Dec-19	Jan-20	Feb-20
2	Dec-19	20	Sep-19	20
3	Oct-19	6	Oct-19		6
4	Sep-19	10	Oct-19		10
5	Sep-19	12	Sep-19	12
6	Nov-19	6				6
Sheet3

Cell Formulas
Range	Formula
D2	=IF($C2<>"",IF($C2=D$1,$B2,""),IF($A2=D$1,$B2,""))

 

[jmacleary]

Darn. I had almost the same forlmula but was struggling to post it. =IF(D$1=IF(ISBLANK($C2),$A2,$C2),$B2,"")

 

[RickyJH]

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?!

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]L[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[TD]R[/TD]
[TD]S[/TD]
[TD]T[/TD]
[TD]U[/TD]
[TD]V[/TD]
[TD]W[/TD]
[TD]X[/TD]
[TD]Y[/TD]
[TD]Z[/TD]
[/TR]
[TR]
[TD]1[/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]
[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][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/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]
[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][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]OPP[/TD]
[TD]JFL[/TD]
[TD]BD[/TD]
[TD]EUC[/TD]
[TD]COM[/TD]
[TD]OPPN[/TD]
[TD]VAL[/TD]
[TD]CAT[/TD]
[TD]Est Order Date[/TD]
[TD]PWIN[/TD]
[TD]RK[/TD]
[TD]Weighted Total[/TD]
[TD]CM[/TD]
[TD]ST[/TD]
[TD]EN[/TD]
[TD]Actual Close Date[/TD]
[TD]SO[/TD]
[TD]Apr-19[/TD]
[TD]May-19[/TD]
[TD]Jun-19[/TD]
[TD]Jul-19[/TD]
[TD]Aug-19[/TD]
[TD]Sep-19[/TD]
[TD]Oct-10[/TD]
[TD]Nov-19[/TD]
[TD]Dec-19[/TD]
[/TR]
[TR]
[TD]4[/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]
[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][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]May-19[/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Sep-19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Oct-19[/TD]
[TD][/TD]
[TD][/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Oct-19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Nov-19[/TD]
[TD][/TD]
[TD][/TD]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Oct-19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

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

 

[RickyJH]

Great thank you. It's not showing any values though on either formula? I have no idea why...

 

[Fluff]

Are the dates actual dates formatted to show mmm-yy or are they text values?

 

[RickyJH]

Text values

 

[Fluff]

In that case make sure they exactly match the headers, especially check for any leading/trailing spaces.

 

[jmacleary]

And as the data is coming from an external crm source, check its not inserting any non-viewable characters into the strings.