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.

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]

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

[Morrigan]

What can be contained in Column A & J?

[Phillycheese5]

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]

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?

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]

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

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

[Phillycheese5]

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]

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