IF function with multiple logic?

R

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

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

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
How about


Book1
ABCDEFGHI
1Est. Closing DateWeighted TotalActual Close DateSep-19Oct-19Nov-19Dec-19Jan-20Feb-20
2Dec-1920Sep-1920
3Oct-196Oct-196
4Sep-1910Oct-1910
5Sep-1912Sep-1912
6Nov-1966
Sheet3
Cell Formulas
RangeFormula
D2=IF($C2<>"",IF($C2=D$1,$B2,""),IF($A2=D$1,$B2,""))
 
Upvote 0
Darn. I had almost the same forlmula but was struggling to post it. =IF(D$1=IF(ISBLANK($C2),$A2,$C2),$B2,"")
 
Last edited:
Upvote 0
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]
 
Upvote 0
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:
Upvote 0
Are the dates actual dates formatted to show mmm-yy or are they text values?
 
Upvote 0
In that case make sure they exactly match the headers, especially check for any leading/trailing spaces.
 
Upvote 0
And as the data is coming from an external crm source, check its not inserting any non-viewable characters into the strings.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
AVERAGEIF TROUBLES
Replies
3
Views
79
AhoyNC
A
J
  • Question Question
auto filling cells based on preset format
Replies
1
Views
35
HighAndWilder
H
F
  • Solved
Formula - Sumif(s)? Sumproduct? etc.
2
Replies
16
Views
296
Fluff
Fluff
V
  • Question Question
SUM XLOOKUP
Replies
5
Views
265
StephenCrump
S
S
  • Solved
lookup return from range of dates
Replies
4
Views
512
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,819
Messages
6,181,153
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