IF AND OR Multi Criteria Formula help

[Lee Anderson]

Hello everyone,

Please go easy on me, I'm new to the forum and very much a self taught (basic) user of excel, most of which is done by trial and error!

Long story short, I have a large workbook which is used for reporting purposes. I have an instance where I need to update a formula to take into consideration various different inputs and don't know how to combine them.

In short, my criteria is as follows;

IF K12=”D*”OR”L*” AND O12=”C*”,L12*100
IF K12=”D*”OR”L*” AND O12=”R*”,L12*150
IF K12=”D*”OR”L*” AND O12=””,L12*150
IF K12=”M*”,L12*20)

I am using this formula at the moment, =IFS(O12="C*",L12*100,O12="R*",L12*150,O12="",L12*150) but due to the addition of the 4th criteria as listed above, I need to add Column K to the formula and don't know how to do so.

Any help would be most gratefully received.

Regards,

Lee

[davsth]

I don't have ifs on this pc so untested

=IFS(and(or(k12="D*", k12="L*"), O12="c*"),L12*100, and(or(k12="D*", k12="L*"), or(O12="r*",o12=””)),L12*150 , K12=”M*”, l12*50)

[Lee Anderson]

I don't have ifs on this pc so untested

=IFS(and(or(k12="D*", k12="L*"), O12="c*"),L12*100, and(or(k12="D*", k12="L*"), or(O12="r*",o12=””)),L12*150 , K12=”M*”, l12*50)

Hi davsth,

Thanks very much for replying. The formula above is giving me a #NAME? error - I've checked everything and it all seems correct. Any ideas?

Thanks,

Lee

[jeffreybrown]

Hi Lee,

If you are really using Excel 2016, how are you getting IFS as a function. I don't see that.

Anyway, please take a look at our forum rules and craft a better title for your query.

[Lee Anderson]

Hi Lee,

If you are really using Excel 2016, how are you getting IFS as a function. I don't see that.

Anyway, please take a look at our forum rules and craft a better title for your query.

Hi Jeff,

Sorry, I'm using Microsfot Excel for Office 365 MSO 32-bit.

I'll look at the rules again and edit my thread title.

Regards,

Lee

[canapone]

Hi

an attempt:

Please Login or Register  to view this content.

K12 O12...are sufficient

Please Login or Register  to view this content.

Regards

[Lee Anderson]

Hi

an attempt:

Please Login or Register  to view this content.

K12 O12...are sufficient

Please Login or Register  to view this content.

Regards

Thanks canapone, unfortunately I'm getting a prompt with "there's a problem with this formula". I appreciate your efforts though - this is driving me nuts!

[canapone]

Ciao

no problem.

Please refer to the attachment


Formulas are in B12:B17

[Pepe Le Mokko]

Administrative Note:

Please don't quote entire posts unnecessarily. They clutter threads and make them hard to read.
Use the "Quick reply" instead
Thanks

[davsth]

A key question is are you looking for anything starting with for example a C or the exact characters C*


you have also not put brackets around your conditions IF K12=”D*”OR”L*” AND O12=”C*”,L12*100 could be sasitifed by the 2 lines below
k12="D*"
k12="L*" and O12="C*"

or
(K12=”D*” OR K12=”L*”) AND O12=”C*”

A small sample will probably get you your answer, especially if you include expected results

[Lee Anderson]

The expanded criteria is as follows:

C*: Cash Bank Transfer
R*: Staff Room (Wallet Credit)
L*: Laptop
D*: Desktop
M*: Monitor

Your formula works in the following circumstances;

K12 = Laptop & O12 = Staff Room (Wallet Credit). It also works when K12 = Laptop & O12 = Cash Bank Transfer. It also works when O12 is blank (these are the only 3 options in the drop down list).

The #NAME? error occurs when I select a different option in K12 (the drop down list can only be Laptop, Desktop, or Monitor).

I've checked my lists and checked the spelling but still can't get round the error when anythign other than Laptop is selected in K12.

[Pepe Le Mokko]

Perhaps post a sample sheet ( see yellow banner) ?

[Lee Anderson]

Here is a sample sheet (I've edited and removed confidential data).

[davsth]

I don't have ifs on my PC here but
=_xlfn.IFS(AND(OR(K12="Desktop", K12="Laptop"), O12="Cash Bank Transfer"),L12*100, AND(OR(K12="Deskptop", K12="Laptop"), OR(O12="Staff Room (Wallet Credit)",O12=””)),L12*150, K12=”Monitor”, L12*20)

is the extra character the problem

[Pepe Le Mokko]

GOT IT !

In your formula, you have two different type of double quotes like O12=”” and "Laptop"
The first kind ” should be replaced everywhere with the straight double quotes " in various places in the formula
This is what triggers the NAME error

[Lee Anderson]

In your formula, you have two different type of double quotes like O12=”” and "Laptop"
The first kind ” should be replaced everywhere with the straight double quotes " in various places in the formula
This is what triggers the NAME error

That's it!!! Thank you!!

I was working across PC and Mac on the same sheet (day and night), I wonder if that's where my error has come from.

Thank you again!!

[davsth]

Good spot Pepe! sharper eyes than me

[Pepe Le Mokko]

And now back to SKYRIM

[Lee Anderson]

Thanks for building the formula for me davsth!

[Pepe Le Mokko]

Good spot Pepe! sharper eyes than me

At almost 70? I doubt it . The "Evaluate formula" tool put me on track...