combine multiple if statements that contain isnumber and search

[karlosoze]

Hi, I am trying to combine multiple if statements that contain isnumber and search

I have employees that can enter a piecework billing code called an M1. if they do 1 M1 they type M1. If they perform 2 of these they would enter it as 2M1. etc. and so on upto a maximum of 10M1.

I have successfully used this formula for a single criteria code but I cannot seem to find how to combine multiple conditions...

=IF(ISNUMBER(SEARCH("E11",AB2)),1,0)

Any help would be greatly appreciated.

Thank you1

 

[phoenixdown]

What are you trying to do exactly? Find out if someone put in 2M1 or 3M1?

Are you are looking to get quantity of M1's?

 

[karlosoze]

Yes, I get an automated report that populates fields with lets say 3M1. If that is the case I need another column in that report to "see" it and enter a "3".

 

[phoenixdown]

If your 3M1 field is in A2, this formula in row A would get the quantity. Would have to change it up if the field includes stuff other than the 3MI. If so, just give me a sample.

=LEFT(A2,(SEARCH("M1",A2))-1)*1

 

[karlosoze]

Phoenixdown, thank you for your help!

the field is populated with various billing codes with variable quantities (the "Ecodes" are exclusive use only codes their quantity will never exceed 1. The "Mcodes" can have have a quantity between 1 and 10 like this

E1,E2,E3,E7,2M1,4M2,5M3 *(there would only be ONE instance of M1 being entered in any one cell. they would not enter M1,M1,M1 etc.)

Within the excel document there is a column for E1 E2 E3 E4 ETC.

The columns for E1 to E11 use the formula I provided in my original post. they look at column AB. If the E1 column finds an E1 is populates it with a 1. The E2 column looks @ AB and if it finds an E2 it populates it with a 1 etc.

The problem I'm having is that I do not know how (or if it is possible) to combine multiple IF ISNUMBER SEARCH criteria in a single formula so that...

If my M1 column sees for example 2M1 it populates it w/ a 2. If it sees NO M1's it populates it with a zero.

I think I have a pretty complex problem. Any help you could provide would be greatly appreciated.

 

[Aladin Akyurek]

Given the following sample in AB...

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

AB​

[/td][/tr]
[tr][td]

2​

[/td][td]E1[/td][/tr]

[tr][td]

3​

[/td][td]E2[/td][/tr]

[tr][td]

4​

[/td][td]E3[/td][/tr]

[tr][td]

5​

[/td][td]E7[/td][/tr]

[tr][td]

6​

[/td][td]2M1[/td][/tr]

[tr][td]

7​

[/td][td]4M2[/td][/tr]

[tr][td]

8​

[/td][td]5M3[/td][/tr]

[tr][td]

9​

[/td][td]M1[/td][/tr]

[tr][td]

10​

[/td][td]M1[/td][/tr]

[tr][td]

11​

[/td][td]M2[/td][/tr]
[/table]

what would you want to count and what would be the result of that count?

 

[karlosoze]

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]E1
[/TD]
[TD]E2
[/TD]
[TD]E3
[/TD]
[TD]E4
[/TD]
[TD]E5
[/TD]
[TD]E6
[/TD]
[TD]E7
[/TD]
[TD]E8
[/TD]
[TD]E9
[/TD]
[TD]E10
[/TD]
[TD]E11
[/TD]
[TD]M1
[/TD]
[TD]M2
[/TD]
[TD]M3
[/TD]
[TD]M4
[/TD]
[TD]TECHS INPUT
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[TD]4
[/TD]
[TD][/TD]
[TD]E1,E2,E3,E7,2M1,3M2,4M3
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]E1,E3,5M1
[/TD]
[/TR]
[TR]
[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]
</tbody>[/TABLE]
This is what I'm trying to achieve. I have the "E codes" working using the formula I put in my original post. *=IF(ISNUMBER(SEARCH("E11",AB2)),1,0)

It's the multiple quantities of the M-codes that are giving me problems.