Problem with compound IF statement

[Phillycheese5]

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]

Maybe try this:

IF(AND(columnA>0, columnJ>0, ISNUMBER(columnA), ISNUMBER(columnJ))
SUM(columnJ),"")


Hope it helps.

 

[Phillycheese5]

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???
Phillycheese

 

[Morrigan]

What can be contained in Column A & J?

 

[Phillycheese5]

Column A is shares, and column J is either realized gains (positive) o
losses (negative). The problem is that I have header rows which repea
throughout the spreadsheet. But none of the headers have numbers i
column A, so that's why I chose it to use if it was greater tha
zero..

 

[Morrigan]

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?

 

[Morrigan]

I just looked at my formula and I realized the problem. Sorry abou
that. Do this instead and you need a helper column because you canno
just apply SUM() inside an IF statement.

ColumnK = IF(AND(columnA>0, columnJ>0, ISNUMBER(columnA)
ISNUMBER(columnJ)), J,"")

Now apply SUM(K:K

 

[Phillycheese5]

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]

I tried the formula with the helper column and still couldn't get it to
work...not sure why...

 

[Morrigan]

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]

Okay, got it!
I didn' t know that the J at the end of the formula needed number
identifying it to a cell (I thought it was some other reference).
Thanks,
Phillycheese

 

[Morrigan]

Glad it worked.

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