Microsoft Office 2007 home and student

I am trying to create a formula with multiple IF statements. If the letter "a" appears on any of the pages, then I want it to show the results of the calculation (B8/N8*O8-P8-(B8-J8)>J8,J8,MAX(0,B8/N8*O8-P8-(B8-J8)

If there is no "a" on any of the sheets, then I want the cell to remain blank.

The formula I have so far (which is definitely wrong) is:

=IF(((OR(VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]December BANK 2009'!$G$6:$T$28,10,)="a", (VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]January BANK 2010'!$G$6:$T$50,10,)="a"), VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]February BANK 2010'!$G$6:$T$35,10,)="a”, IF(B8/N8*O8-P8-(B8-J8)>J8,J8,MAX(0,B8/N8*O8-P8-(B8-J8),"")

Any help with this would be greatly appreciated.

Thanks.

Try this: =IF(OR(VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]December BANK 2009'!$G$6:$T$28,10,)="a",VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]January BANK 2010'!$G$6:$T$50,10,)="a",VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]February BANK 2010'!$G$6:$T$35,10,)="a”),B8/N8*O8-P8-(B8-J8)>J8,J8,MAX(0,B8/N8*O8-P8-(B8-J8),"")

MIKE

Hi, To keep this simple, I have just used three worksheets Dec09, Jan10 & Feb10, and I used the same range on each - you can adjust the ranges as required.

The formula returns "X" if "a" is found in any of the ranges on the specified worksheets.

You can replace "X" with your formula.

Replace "Not found" with "" to display nothingI used COUNTIF() rather than VLOOKUP()

VLOOKUP() only looks for the search item in the first column of the range specified, so VLOOKUP("a", G1:T30 .... only searches for "a" in cells G1 to G30

Also as you needed an exact match, VLOOKUP has to have the FALSE parameter, and if "a" is not found it returns an Error value, which can cause problems.COUNTIF() just counts the number of instances of "a" in the range specified - and for your purpose any value above zero is what you need.

Here is the basic formula:

=IF(COUNTIF('December BANK 2009'!G1:T31,"=a")+COUNTIF('January BANK 2010'!G1:T31,"=a")+COUNTIF('February BANK 2010'!G1:T31,"=a")>0,"X","Not Found")It adds the total number of a's found in the worksheets/ranges and If more than zero, it will use your formula.

Hope this helps.

Regards

Mike - I tried the formula you wrote but it is coming up with an error still. It is reading my formula as "logical 3" still instead of the value if true....

Humar - I don't think this formula will give me the results I am seeking. Unless of course I am reading it wrong...I only want the "a"'s in the specified column (column 10). "a" was used to mark a certain type of payment in the appropriate column in another workbook. If I let it pick up anything with an "a" I'll have figures I don't want.

I only want this formula to calculate if they have "a" in column 10 of that range.

Anita

Another note which may help: The worksheets I am referring to (looking for the "a") - I was using the VLOOKUP function because I was searching for the contract number (found in cell C8) in the other sheet.

The sheet I am searching in...G column shows the contract number, so I need it to find the contract number in that column and then see if there is an "a" in the column mentioned (in this case, 10th column in that range).

Hope this helps a bit more.

Hi, Just change the formula to use column J (i.e. column 10)

=IF(COUNTIF('December BANK 2009'!J1:J31,"=a")+COUNTIF('January BANK 2010'!J1:J31,"=a")+COUNTIF('February BANK 2010'!J1:J31,"=a")>0,"X","Not Found")Regards

Hi, Sorry, you've lost me.

Are you saying that you are

notlooking for "a" in column J (10th column) of the worksheets "January BANK 2010".Can you be specific about the sequence and which workbooks and worksheets are being searched.

Regards

Sorry, did not realize you had two IF statements, try this: =IF(OR(VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]December BANK 2009'!$G$6:$T$28,10,)="a",VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]January BANK 2010'!$G$6:$T$50,10,)="a",VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]February BANK 2010'!$G$6:$T$35,10,)="a”),IF(B8/N8*O8-P8-(B8-J8)>J8,J8,MAX(0,B8/N8*O8-P8-(B8-J8),""),"")

As for Humars' suggestion, try this:

=IF(COUNTIF('December BANK 2009'!J1:J31,"=a")+COUNTIF('January BANK 2010'!J1:J31,"=a")+COUNTIF('February BANK 2010'!J1:J31,"=a")>0,IF(B8/N8*O8-P8-(B8-J8)>J8,J8,MAX(0,B8/N8*O8-P8-(B8-J8),""),"")

MIKE

Thanks for your patience with this. I started with workbook A which has a sheet for each month (i.e. December BANK 2009, January BANK 2010...etc.).

These sheets show detailed transactions by contract number. Column G has their contract number, column P has an "a" in it if they paid for it without financial assistance.

Then, in workbook B I calculate things based on dates, amounts, etc. so I know how much has been earned based on deposits. So in this workbook (B) I have their contract number in column C. In column Q (the one I am trying to create the formula for) I want it to total all of this contracts (see column C) earned amounts (my equation) but only if they selected "a" in column P (of workbook B).

I have a working equation for referring to just one sheet of workbook B. It is

=IF(VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]December BANK 2009'!$G$6:$T$28,10,)="a",IF(B8/N8*O8-P8-(B8-J8)>J8,J8,MAX(0,B8/N8*O8-P8-(B8-J8))),"")

But for each sheet I add, I need to search one extra sheet in workbook B (for each additional month). It is necessary for me to still search the previous months, because they may have paid previously.

I hope this information helps at least a little...

Hi, Try this - for just three of the monthly worksheets.

The formula was in cell Q8, and takes the contract number from C8 (i.e. on the same row as the formula).

=IF(OR(VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]December BANK 2009'!$G$6:$T$28,10,)="a",VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]January BANK 2010'!$G$6:$T$28,10,)="a",VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]February BANK 2010'!$G$6:$T$28,10,)="a"),IF(B8/N8*O8-P8-(B8-J8)>J8,J8,MAX(0,B8/N8*O8-P8-(B8-J8))),"")

Regards

Hi, The difference between my suggested formula and yours is that you have bracketed the VLOOKUP formulas within the OR statement, and the OR statement has not enclosed the three VLOOKUPS in a single set of brackets.

You just need OR(test1,test2,test3)

Each VLOOKUP can be used like this:

VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]January BANK 2010'!$G$6:$T$28,10,)="a"

In this case the result of the VLOOKUP() is compared to the value "a" and returns TRUE or FALSE. OR is true if any one of the VLOOKUPS equals "a"I note that you have not used the last parameter in VLOOKUP, after the 10. This means that VLOOKUP will return a closest match (default for the parameter is TRUE). Your VLOOKUP is actually returning something else when "a" is not present, but as you compare it to "a" the formula still works.

The lookup range $G$6:$T$28 technically only needs to be $G$6:$P$28 because column P is the 10th column from the start of the range - In VLOOKUP, column G (in this case), is column 1, so P is column 10

Regards

Thanks MIke, but it still shows the same error message.

Humar, I am trying your recent suggestion now. Will reply momentarily

When I try the formula, it accepts it but the error message #N/A shows up. I traced the formula and there should actually be an amount in this cell because there is an "a" on the January and February sheet for this contract number (columnn C)

Is there something simple I'm missing here maybe?

Hi, If the contract number does not appear in every month sheet, VLOOKUP will rreturn the #NA error

In each Vlookup add TRUE after the last comma and before the closing bracket.

VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]December BANK 2009'!$G$6:$P$28,10,TRUE)="a"However VLOOKUP with the true parameter requires that all values are in order in the column it searches - column G on each sheet in this case.

Vlookup with FALSE only finds an exact match, and the values (contract numbers) do not have to be in numerical order - but - it returns an #NA error if the value is not present.

If contract numbers are in numerical order, you still have a problem because VLOOKUP will return the next largest contract number.

In your case using FALSE is safer, but if you don't have

allcontract numbers on every month sheet you will get the #NA error.It is possible to trap the #NA error - with an addition to each VLOOKUP.

Let me know what the situation is - do all sheets have all contract numbers & are the contract numbers in order.

Regards

Each sheet has the contract numbers listed for each transaction. They are not, however, in numerical order. Also, a "contract" may be listed more than once if they have made more than one payment. Each time they make a payment, the amount is listed in one column and the a (formated to show as a check mark) is placed in whichever column explains how they received the funding. (ie. self, employer, etc.)

Hi, You can try this:

=IF(OR(IF(ISNA(VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]December BANK 2009'!$G$6:$G$28,1,FALSE)),FALSE,VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]December BANK 2009'!$G$6:$P$28,10,FALSE)="a"),IF(ISNA(VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]January BANK 2010'!$G$6:$G$28,1,FALSE)),FALSE,VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]January BANK 2010'!$G$6:$P$28,10,FALSE)="a"),IF(ISNA(VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]February BANK 2010'!$G$6:$G$28,1,FALSE)),FALSE,VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]February BANK 2010'!$G$6:$P$28,10,FALSE)="a")),IF(B8/N8*O8-P8-(B8-J8)>J8,J8,MAX(0,B8/N8*O8-P8-(B8-J8))),"")It uses the FALSE parameter for VLOOKUP, which ensures only exact matches, and it uses the ISNA function to catch the cases when VLOOKUP does not find a match. Also using the FALSE parameter means that contract numbers do not have to be in order.

I note that you say that you may have more than one instance of a contract number.

VLOOKUP will only find the first instance. If "a" is only present on say a second instance of the contract number VLOOKUP will never return that.

In view of the current complexity I wonder if there is a better way to approach this issue.

It might be better to include some additional processing in the OTTA YEAR END ... workbook. Perhaps an additional page that captures the contract numbers from all month sheets and includes the "a" status. Then the other worksheet only has to do a lookup in one place instead of in all 12 month sheets.

Regards

That formula seems to work great for this cell. You are absolutely correct about adding another sheet for contract numbers. That would be far better. Unfortunately, there are some things in place here that some people do not wish to change. It is a great suggestion and I will work on getting approval and adding that page.

Fo now though, it is the more complicated route...

I entered that formula (with a couple of corrections to the ranges). It ended up being:

=IF(OR(IF(ISNA(VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]December BANK 2009'!$G$6:$G$28,1,FALSE)),FALSE,VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]December BANK 2009'!$G$6:$P$28,10,FALSE)="a"),IF(ISNA(VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]January BANK 2010'!$G$6:$G$50,1,FALSE)),FALSE,VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]January BANK 2010'!$G$6:$P$50,10,FALSE)="a"),IF(ISNA(VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]February BANK 2010'!$G$6:$G$35,1,FALSE)),FALSE,VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]February BANK 2010'!$G$6:$P$35,10,FALSE)="a")),IF(B8/N8*O8-P8-(B8-J8)>J8,J8,MAX(0,B8/N8*O8-P8-(B8-J8))),"")Now, I need to have a similar formula to this, just located over in a different column and referring to column 11 on those sheets instead of 10. I tried adjusting the formula as:

=IF(OR(IF(ISNA(VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]December BANK 2009'!$G$6:$G$28,1,FALSE)),FALSE,VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]December BANK 2009'!$G$6:$P$28,11,FALSE)="a"),IF(ISNA(VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]January BANK 2010'!$G$6:$G$50,1,FALSE)),FALSE,VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]January BANK 2010'!$G$6:$P$50,11,FALSE)="a"),IF(ISNA(VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]February BANK 2010'!$G$6:$G$35,1,FALSE)),FALSE,VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]February BANK 2010'!$G$6:$P$35,11,FALSE)="a")),IF(B8/N8*O8-P8-(B8-J8)>J8,J8,MAX(0,B8/N8*O8-P8-(B8-J8))),"")but it just gives me an error message of "#REF!"

What do I need to do differently? I fear there is something obvious that I do not see...

Hi, At a quick glance, I think that the problem is the range in the second VLOOKUP() in each pair:

$G$6:$P$50,11,FALSEThe offset is 11, but the range only covers 10 columns (G to P)

So now you need to increase the range to 11 columns:

$G$6:$Q$50,11,FALSERegards

Thank you so much. That is exactly what it was. So far, this formula is working just as I had hoped it would. You are nothing short of amazing in my eyes. I will continue to plug in all data and see if it continues to work without problem.

Thank you ever so much for all of your time with this.

Hi, Thanks for the response and your kind words.

I wish you well with your endeavor.

Regards

Humar

Hi again Humar,

This formula works great, but I have just been advised that if cell A23 says "C", "D Straight Truck", "DZ FF", "Z" or "Other" (and also has the letter "a" appearing on the sheets mentioned), then it needs to equal what is shown in cell I23. If A23 says anything else, then the formula needs to be as it was.I have been trying to adjust it and this is what I have so far:

=IF(OR(A23=“C”,A23=“D Straight Truck”,A23=“DZ FF”,A23=“Z”,A23=“Other”), IF(OR(IF(ISNA(VLOOKUP(C23,'[OTTA YEAR END 2010.xlsx]December BANK 2009'!$G$7:$G$110,1,FALSE)),FALSE,VLOOKUP(C23,'[OTTA YEAR END 2010.xlsx]December BANK 2009'!$G$7:$T$110,10,FALSE)="a"),IF(ISNA(VLOOKUP(C23,'[OTTA YEAR END 2010.xlsx]January BANK 2010'!$G$7:$G$110,1,FALSE)),FALSE,VLOOKUP(C23,'[OTTA YEAR END 2010.xlsx]January BANK 2010'!$G$7:$T$110,10,FALSE)="a")),I23, IF(OR(IF(ISNA(VLOOKUP(C23,'[OTTA YEAR END 2010.xlsx]December BANK 2009'!$G$7:$G$110,1,FALSE)),FALSE,VLOOKUP(C23,'[OTTA YEAR END 2010.xlsx]December BANK 2009'!$G$7:$T$110,10,FALSE)="a"),IF(ISNA(VLOOKUP(C23,'[OTTA YEAR END 2010.xlsx]January BANK 2010'!$G$7:$G$110,1,FALSE)),FALSE,VLOOKUP(C23,'[OTTA YEAR END 2010.xlsx]January BANK 2010'!$G$7:$T$110,10,FALSE)="a")),IF(B23/N23*O23-P23-(B23-J23)>J23,J23,MAX(0,B23/N23*O23-P23-(B23-J23))),"")

Excel is telling me I am missing a parenthesis. Also, I am not sure that this is the best way to complete the formula.

I would love it if you could take a look for me.

Hi, From what I think you require :)

You have two groups of OR statements -

One is the original OR which tests for "a" on one of the worksheets and

the Other is a new test of possible values in cell A23 ("C", "D" etc.)As the new OR statements have to be false i.e., none of them are true when one of the original OR statements for "a"

istrue, then we negate the first OR, so NOT(OR(,,,))Then we can AND the NOT(OR() with the original OR

so if none of the new OR tests is true AND one of the original OR statements is true, we use the original formulaIn the full original formula if the original OR tests for "a" were all false we returned ""

Now when either one of the new tests is true or none of the old tests for "a" is true, we return the value in I23.Please try this:

=IF(AND(NOT(OR(A23="C",A23="D Straight Truck",A23="DZ FF",A23="Z",A23="Other")),OR(IF(ISNA(VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]December BANK 2009'!$G$6:$G$28,1,FALSE)),FALSE,VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]December BANK 2009'!$G$6:$Q$28,11,FALSE)="a"),IF(ISNA(VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]January BANK 2010'!$G$6:$G$50,1,FALSE)),FALSE,VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]January BANK 2010'!$G$6:$Q$50,11,FALSE)="a"),IF(ISNA(VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]February BANK 2010'!$G$6:$G$35,1,FALSE)),FALSE,VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]February BANK 2010'!$G$6:$Q$35,11,FALSE)="a"))),IF(B8/N8*O8-P8-(B8-J8)>J8,J8,MAX(0,B8/N8*O8-P8-(B8-J8))),I23)It breaks down like this:

=IF(

the if test:

AND(

the first of two And's (a Not/Or):

NOT(OR(A23="C",A23="D Straight Truck",A23="DZ FF",A23="Z",A23="Other")),

the second of two And's (an Or consisting of a series of Vlookups for the value "a"):

OR(IF(ISNA(VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]December BANK 2009'!$G$6:$G$28,1,FALSE)),FALSE,VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]December BANK 2009'!$G$6:$Q$28,11,FALSE)="a"),IF(ISNA(VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]January BANK 2010'!$G$6:$G$50,1,FALSE)),FALSE,VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]January BANK 2010'!$G$6:$Q$50,11,FALSE)="a"),IF(ISNA(VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]February BANK 2010'!$G$6:$G$35,1,FALSE)),FALSE,VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]February BANK 2010'!$G$6:$Q$35,11,FALSE)="a")

) end of And

) end of If test

Result if true:

,IF(B8/N8*O8-P8-(B8-J8)>J8,J8,MAX(0,B8/N8*O8-P8-(B8-J8))),

result if false

I23)In summary:

IF(AND(NOT(OR(tests of A23)),OR(vlookups for "a")),result if true, result if false)Regards

Humar

We almost have it. Whether A23 is true or false, I still need vlookup to determine if there will be a value in that cell. The value in A23 will determine what the result will be (I23 or formula) If A23 tests are true AND if vlookups for "a", then will be I23.

If A23 tests are false BUT vllookups for "a" are true, then the original formula will run:

IF(B23/N23*O23-P23-(B23-J23)>J23,J23,MAX(0,B23/N23*O23-P23-(B23-J23))),"")If vlookups for "a" are false, then the cell needs to remain blank.

Hi, Here we go again!

The formual starts by testiing the worksheets for "a"

If false it returns a blank cell ""If true it tests A23

If A23 contains one of the values C, D etc, the result returned is I23

If A23 does not contain one of the values C, D etc the original formula is used.Here is the formula:

=IF(OR(IF(ISNA(VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]December BANK 2009'!$G$6:$G$28,1,FALSE)),FALSE,VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]December BANK 2009'!$G$6:$Q$28,11,FALSE)="a"),IF(ISNA(VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]January BANK 2010'!$G$6:$G$50,1,FALSE)),FALSE,VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]January BANK 2010'!$G$6:$Q$50,11,FALSE)="a"),IF(ISNA(VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]February BANK 2010'!$G$6:$G$35,1,FALSE)),FALSE,VLOOKUP(C8,'[OTTA YEAR END 2010.xlsx]February BANK 2010'!$G$6:$Q$35,11,FALSE)="a")),IF(OR(A23="C",A23="D Straight Truck",A23="DZ FF",A23="Z",A23="Other"),I23,IF(B8/N8*O8-P8-(B8-J8)>J8,J8,MAX(0,B8/N8*O8-P8-(B8-J8)))),"")The structure is:

=IF(OR(tests for "a"), if tests for "a" are true then tests A23 IF(OR(tests A23), if A23 test is true then returns I23, if A23 test is false then runs the original formula: IF(B8/N8*O8-P8-(B8-J8)>J8,J8,MAX(0,B8/N8*O8-P8-(B8-J8)))) If tests for "a" are false then return blank: ,"")Regards

Thanks Humar! That worked fabulous. :-)

Hi, So glad to hear it worked - I had started to have nightmares where I was searching for missing parentheses ...

Have a great day

Regards

Humar

Those are never good dreams Humar! :-) I do have another question regarding the saving of this workbook(s).

Should I submit a separate question or can I just ask you here?

Hi, Please start a new post as it's a different issue.

Regards

Ask Your Question

Weekly Poll

Do you think Google should sell budget phones in the US?

Discuss in The Lounge

Poll History