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!
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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?!

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

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,539
Latest member
alex78

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