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

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

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

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

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

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

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

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

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

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

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

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