IF Function 20 scenarios =(

Hi, welcome to the forum

From what I can see, something like...
if(P3=Yes,10,0)+if(E3=billable,)3,0)+............
See where im going on this?

Another option might be to use a set of small tables, and use vlookup or something similar.

Maybe upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

Read all this first before doing anything

I notice if P3="Yes" then 10 is added otherwise nothing is added so that cuts out half the IFs already
Create a table to lookup E3 and G3, the three extra columns will have 1s or 0s to multiply O3 or Q3 depending on the combination of the first two columns.
Sounds complicated but just follow these instructions and it should work (I think)

So you can replace all those IFs using this

Set up a matrix 10 rows by 5 columns in Sheet2!A1 like this

Billable,Paper Mail,1,1,0
Non-Billable,Paper Mail,0,1,0
Billable,CD/USB Mail,0,1,1
Non-Billable,CD/USB Mail,0,1,0
Billable,Electronic,0,0,1
Non-Billable,Electronic,0,0,0
Billable,Walk-in Paper,1,0,0
Non-Billable,Walk-in Paper,0,0,0
Billable,Walk-in CD/USB,0,0,1
Non-Billable,Walk-in CD/USB,0,0,0

You can copy this into a blank spreadsheet, let's say Sheet2 starting at A1. You may need to use text to Columns with a comma separater so you end up with 10 rows and 5 columns

Then your formula would be (slightly tested)

=INDEX(Sheet2!A$1:E$10,MATCH(E3,IF(G3=Sheet2!B$1:B$10,Sheet2!A$1:A$10),0),3)*O3+INDEX(Sheet2!A$1:E$10,MATCH(E3,IF(G3=Sheet2!B$1:B$10,Sheet2!A$1:A$10),0),4)*Q3+INDEX(Sheet2!A$1:E$10,MATCH(E3,IF(G3=Sheet2!B$1:B$10,Sheet2!A$1:A$10),0),5)*6.5+(P3="Yes")*10
Array formula, use Ctrl-Shift-Enter

Originally Posted by szucchari

I am getting an Error message : #N/A , please let me know If I copied something wrong.

It should be an array formula as stated.
If you enter it as a normal formula you get a #N/A error.

Array formula, use Ctrl-Shift-Enter

I copied the data to Sheet2!A1, did a Text to columns using comma as delimiter so the matrix now covers Sheet2!A1:E10

In Sheet1!E3 I put Billable
In Sheet1!G3 I put Electronic

I entered the formula in Sheet1!A1 as an array formula and it returned the value 6.5 which is correct as P3 is blank (not Yes)

Another approach:

I'd also start from correcting
If E3 = "Non-Billable" and G3 = "Walk-in CD/USB" and P3 = "Yes" then R3= 0
It's probably for P3 = "No"

Then I'd notice that P3 = yes always adds 10 to result (compared to P3 = No),

so we have only 10 variants

if(P3 = "Yes",10,0) +

If E3 = "Billable" and G3 = "Paper Mail" then R3= O3+Q3
If E3 = "Non-Billable" and G3 = "Paper Mail" then R3 =Q3
If E3 = "Billable" and G3 = "CD/USB Mail" then R3=6.50+Q3
If E3 = "Non-Billable" and G3 = "CD/USB Mail" then R3=Q3
If E3 = "Billable" and G3 = "Electronic" then R3=6.50
If E3 = "Non-Billable" and G3 = "Electronic" then R3= 0
If E3 = "Billable" and G3 = "Walk-in Paper" then R3= O3
If E3 = "Non-Billable" and G3 = "Walk-in Paper" then R3= 0
If E3 = "Billable" and G3 = "Walk-in CD/USB" then R3= 6.50
If E3 = "Non-Billable" and G3 = "Walk-in CD/USB" then R3= 0


moreover it seems that R3 = 0 (or 10 if P3 = yes) for E3 non-billable and 3 values of G3 and R3 =Q3 for other 2 values of G3.

For E3 = "Billable" we have only 2 cases (in G3) where R3 = 6.5



Let's rearrange the above:

if(P3 = "Yes",10,0) +

If E3 = "Non-Billable" and (G3 = "Paper Mail" or G3 = "CD/USB Mail") then R3=Q3
If E3 = "Non-Billable" and G3 is different from above then R3= 0

If E3 = "Billable" and G3 = "Paper Mail" then R3= O3+Q3
If E3 = "Billable" and G3 = "CD/USB Mail" then R3=6.50+Q3
If E3 = "Billable" and G3 = "Walk-in Paper" then R3= O3
If E3 = "Billable" and G3 is different from above then R3=6.50

so we can write it (still informal way):

if(P3 = "Yes",10,0) + If(E3 = "Non-Billable"

then

if (G3 = "Paper Mail" or G3 = "CD/USB Mail") then R3=Q3
else
If G3 = "Paper Mail" then R3= O3+Q3
If G3 = "CD/USB Mail" then R3=6.50+Q3
If G3 = "Walk-in Paper" then R3= O3
If G3 is different from above then R3=6.50
)

and now real excel formula for R3

Formula:

= if(P3 = "Yes",10,0) + If(E3 = "Non-Billable" , if (G3 = "Paper Mail" or G3 = "CD/USB Mail", Q3,0), If( G3 = "Paper Mail", O3+Q3, If( G3 = "CD/USB Mail", 6.50+Q3, if(G3 = "Walk-in Paper", O3,6.50 ))))

To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

Just in case - have you also noted less elegant than array formula, but probably "more traditional" solution presented in post #4 https://www.excelforum.com/excel-for...ml#post5160646 ?