Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Problem with compound IF statement
Here's what I'm trying to say: If column A is greater than 0, then sum column J if J is greater than 0. My data download is generally 500-700 rows, so I'm using 1000 to capture all data. However, my data has repeating headers with text and Excel seems to treat that as greater than 0 also. I do have a unique date range field in column E that I can use (in MM/DD/YYYY format) to say if it's greater than 01/01/1900 , but I couldn't get that to work either. Any help would be appreciated. Thanks, Phillycheese5 -- Phillycheese5 ------------------------------------------------------------------------ Phillycheese5's Profile: http://www.excelforum.com/member.php...o&userid=24196 View this thread: http://www.excelforum.com/showthread...hreadid=388045 |
#2
|
|||
|
|||
Maybe try this: IF(AND(columnA0, columnJ0, ISNUMBER(columnA), ISNUMBER(columnJ)), SUM(columnJ),"") Hope it helps. Phillycheese5 Wrote: Here's what I'm trying to say: If column A is greater than 0, then sum column J if J is greater than 0. My data download is generally 500-700 rows, so I'm using 1000 to capture all data. However, my data has repeating headers with text and Excel seems to treat that as greater than 0 also. I do have a unique date range field in column E that I can use (in MM/DD/YYYY format) to say if it's greater than 01/01/1900 , but I couldn't get that to work either. Any help would be appreciated. Thanks, Phillycheese5 -- Morrigan ------------------------------------------------------------------------ Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094 View this thread: http://www.excelforum.com/showthread...hreadid=388045 |
#3
|
|||
|
|||
Morrigan, I did get a number with your formula (I substituted "A:A" for "columnA" and so on) but the result did not tie to what I did manually...... Mabye using the date field is a better way to go??? Phillycheese5 -- Phillycheese5 ------------------------------------------------------------------------ Phillycheese5's Profile: http://www.excelforum.com/member.php...o&userid=24196 View this thread: http://www.excelforum.com/showthread...hreadid=388045 |
#4
|
|||
|
|||
What can be contained in Column A & J? -- Morrigan ------------------------------------------------------------------------ Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094 View this thread: http://www.excelforum.com/showthread...hreadid=388045 |
#5
|
|||
|
|||
Column A is shares, and column J is either realized gains (positive) or losses (negative). The problem is that I have header rows which repeat throughout the spreadsheet. But none of the headers have numbers in column A, so that's why I chose it to use if it was greater than zero... -- Phillycheese5 ------------------------------------------------------------------------ Phillycheese5's Profile: http://www.excelforum.com/member.php...o&userid=24196 View this thread: http://www.excelforum.com/showthread...hreadid=388045 |
#6
|
|||
|
|||
Is the header rows there for a purpose? Can it be taken out and just freeze panel to keep one header rows and use "print repeat top rows" for printing? Phillycheese5 Wrote: Column A is shares, and column J is either realized gains (positive) or losses (negative). The problem is that I have header rows which repeat throughout the spreadsheet. But none of the headers have numbers in column A, so that's why I chose it to use if it was greater than zero... -- Morrigan ------------------------------------------------------------------------ Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094 View this thread: http://www.excelforum.com/showthread...hreadid=388045 |
#7
|
|||
|
|||
I just looked at my formula and I realized the problem. Sorry about that. Do this instead and you need a helper column because you cannot just apply SUM() inside an IF statement. ColumnK = IF(AND(columnA0, columnJ0, ISNUMBER(columnA), ISNUMBER(columnJ)), J,"") Now apply SUM(K:K) -- Morrigan ------------------------------------------------------------------------ Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094 View this thread: http://www.excelforum.com/showthread...hreadid=388045 |
#8
|
|||
|
|||
The dataset is a download from a program that doesn't allow me flexibility with the text headers, and is not at regular intervals since the length of the dataset varies and it has subtotal lines along the way. The only unique fields is the shares in column A (as an integer) and the date (in column E) as MM/DD/YYYY. -- Phillycheese5 ------------------------------------------------------------------------ Phillycheese5's Profile: http://www.excelforum.com/member.php...o&userid=24196 View this thread: http://www.excelforum.com/showthread...hreadid=388045 |
#9
|
|||
|
|||
I tried the formula with the helper column and still couldn't get it to work...not sure why... -- Phillycheese5 ------------------------------------------------------------------------ Phillycheese5's Profile: http://www.excelforum.com/member.php...o&userid=24196 View this thread: http://www.excelforum.com/showthread...hreadid=388045 |
#10
|
|||
|
|||
It worked for me. Here is what I tried: Header Header Helper 4 5 5 6 -6 -7 7 Header Header 8 -8 9 9 9 SUM() = 14 What kind of result are you getting? Phillycheese5 Wrote: I tried the formula with the helper column and still couldn't get it to work...not sure why... -- Morrigan ------------------------------------------------------------------------ Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094 View this thread: http://www.excelforum.com/showthread...hreadid=388045 |
#11
|
|||
|
|||
Okay, got it! I didn' t know that the J at the end of the formula needed numbers identifying it to a cell (I thought it was some other reference). Thanks, Phillycheese5 -- Phillycheese5 ------------------------------------------------------------------------ Phillycheese5's Profile: http://www.excelforum.com/member.php...o&userid=24196 View this thread: http://www.excelforum.com/showthread...hreadid=388045 |
#12
|
|||
|
|||
Glad it worked. Phillycheese5 Wrote: Okay, got it! I didn' t know that the J at the end of the formula needed numbers identifying it to a cell (I thought it was some other reference). Thanks, Phillycheese5 -- Morrigan ------------------------------------------------------------------------ Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094 View this thread: http://www.excelforum.com/showthread...hreadid=388045 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF Statement Problem | Excel Worksheet Functions | |||
If Statement Problem | Excel Worksheet Functions | |||
Fundamental problem with IF statement | Excel Worksheet Functions | |||
Fundamental problem with IF statement | Excel Worksheet Functions | |||
IF statement problem | Excel Worksheet Functions |