Multiple IF statements and separate formulas

[Figero21]

I am trying to build a formula that will verify a word in the first section and in another section, however if the word was to change the. It will produce a separate result. I have been able to complete it for one result but once I try and build upon it the formula no longer works. It says I have too many arguments.

=IF(COUNT(SEARCH({"5M"},I4)),IF(COUNT(SEARCH({"Actual"},AI4)),IF(AND(S4>Sheet3!C$19,S4<Sheet3!D$19),"Pass","Fail")),IF(COUNT(SEARCH({"5M"},I4)),IF(COUNT(SEARCH({"False load"},AI4)),IF(AND(S4>Sheet3!E$19,S4<Sheet3!F$19),"Pass","Fail"))),IF(COUNT(SEARCH({"7M"},I4)),IF(COUNT(SEARCH({"Actual"},AI4)),IF(AND(S4>Sheet3!C$20,S4<Sheet3!D$20),"Pass","Fail")),IF(COUNT(SEARCH({"7M"},I4)),IF(COUNT(SEARCH({"False load"},AI4)),IF(AND(S4>Sheet3!E$20,S4<Sheet3!F$20),"Pass","Fail")))))

 

[NiMip]

I think your forum post is trying to turn your formula into HTML. Can you put a space after what I assume is an "<" after the S4?

 

[Figero21]

Here is the formula again

=IF(COUNT(SEARCH({"5M"},I4)),IF(COUNT(SEARCH({"Actual"},AI4)),IF(AND(S4 > Sheet3!C$19,S4 < Sheet3!D$19),"Pass","Fail")),IF(COUNT(SEARCH({"5M"},I4)),IF(COUNT(SEARCH({"False load"},AI4)),IF(AND(S4 > Sheet3!C$19,S4 < Sheet3!D$19),"Pass","Fail"))))

I am getting a False message instead of getting a pass fail. or if I make some changes then I am getting a to many arguments

 

[Marcílio_Lobão]

Figero21, Good evening.

Try to use:

=IF(AND(COUNT(SEARCH("5M",I4)),COUNT(SEARCH({"Actual","False Load"},A14)), S4 > Sheet3!C$19, S4 < Sheet3!D$19),"Pass","Fail")
<sheet3!d$19),"pass","fail")
<sheet3!d$19),"pass","fail")

Please, tell us if it worked as you desired.

I hope it helps.</sheet3!d$19),"pass","fail")
</sheet3!d$19),"pass","fail")

 

[Figero21]

<sheet3!d$19),"pass","fail")
That didn't work unfortunately. When you change the search to find "False load" instead of "Actual" the search criteria also changes. I need it to find data in E$19 and F$19 if it is "false load" and c$19 and D$19 if it is "actual"

---

I K M O Q S U W
[TABLE="width: 1545"]
<tbody>[TR]
[TD][/TD]
[TD]Meter Size[/TD]
[TD][/TD]
[TD]Fixed Factor[/TD]
[TD][/TD]
[TD]Differential[/TD]
[TD][/TD]
[TD]Meter Loc[/TD]
[TD][/TD]
[TD]Flow Seconds[/TD]
[TD][/TD]
[TD]Flowrate[/TD]
[TD][/TD]
[TD]Pass/Fail CCF[/TD]
[TD][/TD]
[TD]Meter Reading[/TD]
[TD][/TD]
[TD]Meter CCF[/TD]
[TD][/TD]
[TD]Pressure[/TD]
[TD][/TD]
[TD]Original Fixed Factor[/TD]
[TD][/TD]
[TD]High[/TD]
[TD][/TD]
[TD]Low[/TD]
[TD]False Load / Actual Load[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Reg Type[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]5M[/TD]
[TD][/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Out Right[/TD]
[TD][/TD]
[TD]41[/TD]
[TD][/TD]
[TD]878[/TD]
[TD][/TD]
[TD]Pass[/TD]
[TD][/TD]
[TD]74568[/TD]
[TD][/TD]
[TD]10[/TD]
[TD][/TD]
[TD]1.22[/TD]
[TD][/TD]
[TD]1.19[/TD]
[TD][/TD]
[TD]1.3459[/TD]
[TD][/TD]
[TD]1.0341[/TD]
[TD]Actual[/TD]
[TD][/TD]
[TD]Insert Equation[/TD]
[TD][/TD]
[TD]301[/TD]
[/TR]
</tbody>[/TABLE]
</sheet3!d$19),"pass","fail")


Sheet 3

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]5M[/TD]
[TD]10[/TD]
[TD]400[/TD]
[TD]4500[/TD]
[TD]400[/TD]
[TD]1000[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 427"]
<tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64, align: right"][/TD]
[TD="width: 81, align: right"][/TD]
[TD="width: 81, align: right"][/TD]
[TD="width: 64, align: right"][/TD]
[TD="width: 73, align: right"][/TD]
[/TR]
</tbody>[/TABLE]

=IF(AND(COUNT(SEARCH("5M",I4)),COUNT(SEARCH({"Actual","False Load"},AI4)), S4>Sheet3!C$19, S4<sheet3!d$19),"pass","fail")< html=""></sheet3!d$19),"pass","fail")<>

 

[Figero21]

Sorry full formula didn't save with last post.

=IF(AND(COUNT(SEARCH("5M",I4)),COUNT(SEARCH({"Actual","False Load"},AI4)), S4>Sheet3!C$19, S4<Sheet3!D$19),"Pass","Fail")

 

[Marcílio_Lobão]

Figero21, Good evening.

My suggested formula was built based on your original formula.

Since you have now changed the search range, you need a new logic and a new formula.

Try to use:

=IF(COUNT(SEARCH("5M",I4)),IF(OR(AND(COUNT(SEARCH("Actual",AI4)),S4 > Sheet3!C$19,S4 < Sheet3!D$19), AND(COUNT(SEARCH("False load",AI4)),S4 > Sheet3!E$19, S4 < Sheet3!F$19)),"Pass","Fail"), "")

Please, tell us if it worked for you now.
I hope it helps.

 

[Figero21]

Marcílio_Lobão,

=IF(COUNT(SEARCH("5M",I4)),IF(OR(AND(COUNT(SEARCH("Actual",AI4)),S4 > Sheet3!C$19,S4 < Sheet3!D$19), AND(COUNT(SEARCH("False load",AI4)),S4 > Sheet3!E$19, S4 < Sheet3!F$19)),"Pass","Fail"), "")

Thanks, That formula worked amazing. I had one other problem with the formula. How could I duplicate this formula so that if I were to also change the word from 5M to 11M it would change the selection also? I need all of this info to be added to the same cell.

Thanks

 

[Figero21]

Marcílio_Lobão,


My additional problem is that the first word search has several different changes that need to change and take effect also. I have a total of 23 different words to put into that section. Each one had a different factor to it which changes the end amount. As long as I can change the 5M to 11M or 7M or 750 ect then it should change for the rest. When I try and duplicate it in the same cell it isn't working.

Thanks.

 

[Marcílio_Lobão]

Figero21, Good morning.

It seems that your need is much greater than you are passing to us gradually.

To facilitate our help to you, save your file on a free website, www.sendspace.com and place the download link here.

Manually put some answers there as you would like them to appear.

Seeing the total of the work we will be able to offer you a faster and conclusive help.

Help us to help you.