# Posts by opeyemi1

• ## Nest Lookup With IF AND Functions

Re: Nest Lookup With IF AND Functions

Thanks AAE!
It's a good thing you asked that question because I just noticed something. I am not comparing the sale date and the purchase date for each row. But before it says "possible" the sale date for that row must be greater than the purchase date for another row that has a d cell that matches d2 and also has a c cell that is a positive quantity. So, row 2 should match rows 47 to 99 (the highlighted rows). How will this formula change?
Thanks again!

• ## Nest Lookup With IF AND Functions

Re: Nest Lookup With IF AND Functions

Hello all,

I have this formula from below:

=IF(LEN(B2)=0,"",IF(AND(VLOOKUP(A2,\$A3:\$D\$99,4,FALSE)=D2,VLOOKUP(A2,\$A3:\$C\$99,3,FALSE)>0,B2>A2),"Possible",""))

But I was wondering how I could change it so that it would only give "possible" when all the above is true but only when C2 is negative and the D cell that matches d2 should have positive cell C amount.

I tried this:
=IF(LEN(B2)=0,"",IF(C2>0,"",IF(AND(C2<0,VLOOKUP(A2,\$A3:\$D\$99,4,FALSE)=""&D3&"",VLOOKUP(A2,\$A3:\$C\$99,3,FALSE)>0,B2>A2),"Possible","")))

but it is giving me "possible" even when D2 does not match any other d cell for example

Thanks!

• ## Nest Lookup With IF AND Functions

Re: Lookup And If Statements

Thanks so much Brian. There was a space after the name in my cell D2 and so it wasn't matching the name with others. Thanks again! I appreciate it.

• ## Nest Lookup With IF AND Functions

Re: Lookup And If Statements

Hi Brian, Sorry I made a mistake on the previous post about not getting the result. when I copied the formula down from F2 to f99, only F3 shows "possible", but both F2 and F3 should have "possible". I also copied F2 and created a new row 4, so the information in F2 is now duplicated also in F4 and so the formula should show "possible" in F2, F3 &F4, but it's still not working. Do you know if it's a problem with my excel spreadsheet? or is the formula not all inclusive? Thanks!

• ## Nest Lookup With IF AND Functions

Re: Lookup And If Statements

Hi Brian,

Thanks. I tried the formula you provided on the sample xls that I attached. It only places "possible" on the first cell, but both F2 and F3 should have possible since they meet the criteria. How can I change the formula? Thank you!

• ## Nest Lookup With IF AND Functions

Hello, I posted thought I posted this earlier, but can't find my posting. I hope you can help this. I am not sure if I need a macro or just formulas will do.

I have a spreadsheet that is by:
Purchase Date (column A)
Sales Date(column B)
Quantity(column C)
Name (column D)
Gain/loss (column E)

The spreadsheet is sorted by name in ascending order and also by quantity in ascending order.

Assuming row 1 is the heading. If D2 (name)= the same as another D cell and it's quantity i.e. C2 is negative, while the other D cell with the same name as D2 has a positive quantity i.e. corresponding C cell, and if the date in B2 is greater than date in column A of the other cell where it's D cell matches D2 and has a positive quantity, then in I would like "possible" to display in F2.
I have included an attachment to better illustrate what I have described above, because I am not sure if I am clear enough.

• ## Lookup Part Text

Re: Vlookup Or Similar

Thanks so much Domenic, that works perfectly!

• ## Lookup Part Text

Hello all,

I am not sure if a vlookup can do the following.

I have a spreadsheet in excel with lots of rows. In column A I have some descriptions such as:

British Pound
Danish Krone
Japanese Yen

I have created another spreadsheet 2 columns that would have something like:
column A/columnB
British/Yes
Danish/No
Japananese/Yes

This 2nd spreadsheet can grow and have more lines.
In column Z of the 1st spreadsheet, I want a formula to look at the second spreadsheet and if any word in the column A of the 1st spreadsheet matches the word in column A of the second spreadsheet, I want it to the value in column B of the second spreadsheet.

I am not sure how to do this partial matching...

Thanks!

• ## Sum Cells Where Corresponding Cells Meet Condition

Re: Sum Cells Where Corresponding Cells Meet Condition

Hi Dave, thanks. I am not sure how to use the SUMIf formula.
The sumproduct suggested by daddylongleg works for the cells I referenced earlier but I am not sure of how to expand it when I have non consecutive cells.
I would like the formula to add in 1 sum:

a29+g29 if g29<>0
a30+g30 if g30<>0
a35+g35 if g35<>0
a36+g36 if g36<>0
a39+g39 if g39<>0
a40+g40 if g40<>0

but if g29 = 0, then a29 should not be added to the sum, so also for the other cells.

Thanks again.

• ## Sum Cells Where Corresponding Cells Meet Condition

Re: Conditional Statements

Quote from ltzhao

I am a little confused, are you looking to get 1 sum or 3 seperate sums?

I am looking to get 1 sum.[hr]*[/hr] Auto Merged Post;[dl]*[/dl]

Perhaps try

=SUMPRODUCT(--(B1:B3<>0),A1:A3+B1:B3)

Thanks so much, this is what I was looking for![hr]*[/hr] Auto Merged Post;[dl]*[/dl]

Perhaps try

=SUMPRODUCT(--(B1:B3<>0),A1:A3+B1:B3)

Thanks so much, this is what I was looking for![hr]*[/hr] Auto Merged Post;[dl]*[/dl]

Perhaps try

=SUMPRODUCT(--(B1:B3<>0),A1:A3+B1:B3)

Just another related question, what if instead of B1:B3 above, I have g29:g30, g35:g36, g39:g40 and the corresponding A cells, how would I change this formula? thanks again!

• ## Sum Cells Where Corresponding Cells Meet Condition

hello,

I think I need an if statement in excel, but not sure how to go about it.

I have items in A1, A2, A3 and B1, B2, B3

I would like to sum A1+B1 if B1<>0, A2+B2 if B2<>0, A3+B3 if B3<>0, all in one statement. If B1 = 0, then a1 should not be added to the equation, also if B2 = 0, a2 should not be added to the equation, if B3 = 0, a3 should not be added to the equation.

Thanks!

• ## IF AND OR Functions

Hello,

I am trying to put together a formula in excel, here is what I have so far,
=IF(OR(W4="G",W4="SG"),(U4-AA4),0)

But I would like to add to this formula such that when W4="SG" and U4>0, then it should return 0.
I tried doing this: =IF((AND(W2="sg",U2<0)OR(W2="G",W2="SG")),(U2-AA2),0)

but it's obviously not correct.

Thanks!

• ## Sum Same Cell Across Multiple Worksheets

Re: Add Same Cell From Different Worksheets

Thanks much guys! that's exactly what I was looking for...thank you.

• ## Sum Same Cell Across Multiple Worksheets

Hello all,

I have about 100 worksheets in my excel workbook and each sheet is identical. I would like to create a control sheet that would add the cells of the different worksheets together and place it in the control. I tried doing it manually but found out quickly that it would take forever since I have a about 10 cells that I need to put on the control sheet.

For example on the control sheet in J11 I want to put in: ='1'!J11+'2'!J11......'100'!J11

Is there a faster way to do this? Thanks for your help!

• ## #VALUE! Error With Date Calculations

Re: Date Value Error

You're right! it worked. Thanks much for your help!

• ## #VALUE! Error With Date Calculations

Re: Date Value Error

Hello all,

I tried the =days360(a1,b1), but still getting the #value! error. I also tried +a2-b2, and I got #value! error except in 2 cells that it gave me the correct days. I also tried the datevalue(a2) to convert the cells and I got teh #value! error. Is there a way out of this at all?

Thanks!

• ## #VALUE! Error With Date Calculations

Hello,

I have 2 columns of dates and would like to find the difference in 2 dates in column a and b for example. when I use the formula a2-b2 I get #Value! or #Name? in these cells.
The cells appear to be in format dd/mm/yyyy. I tried formatting them to mm/dd/yyyy, but the dates are not changing at all. I have quite a few of these spreadsheets so it won't be practical to retype each cell individually.

Thank!

• ## Iserror Formula

Re: Iserror Formula

Worked Perfect! Thanks so much!

Quote from sweep

Try this?

IF(ISERROR(IF(G15<0,(I15+G15),I15)),"",IF(G15<0,(I15+G15),I15))

Dave

• ## Iserror Formula

Hello all,
I am having a problem with my iserror formula, I think I am missing something,

Here is my original formula:

IF(G15<0,(I15+G15),I15)

But then I get #Value in some cells, I want to be able to get rid of the #Value and so I used this formula:

IF(ISERROR(G15<0,(I15+G15),I15)),"",((G15<0,(I15+G15),I15)))

But excel is saying there is an error with the formula, I have been pondering this for a while now, hope you can help.

Thanks,

• ## Formula Not Updating

Re: Formula Not Updating

if(A2<B2,"0,"17")