 Status
Not open for further replies.
1 - 9 of 9 Posts

#### noveltech

·
##### Registered
Joined
·
73 Posts
Discussion Starter · ·
I am attempting to move from Quattro Pro 8 to Excel 2002.

With Quattro Pro...

@SUMIF(A601..A621,"MO1",\$J601..\$J621+K601..K621+L601..L621)

equals 2993.94

With excel same formular produces error..

All cells have same text and/or numbers.

I want the Sum Total of {J601:J621 + K601:K621 + L601:L621}
if {A601:A621} contains MO1

@SUMIF(A601:A621,"MO1",J601:L621)

equals 0 and it should equal 2993.94

All feedback welcomed...can not find assistance with the excel help functions with this specific problem.

Thank you, noveltech.

#### RandyG

·
##### Registered
Joined
·
7,762 Posts
instead of your double period ".." to mean a range of cells, use the colon ":". see if that helps.

#### noveltech

·
##### Registered
Joined
·
73 Posts
Discussion Starter · ·
Thanks RandyG

My formular has : instead of ..

Typo when I was typing the previous message.

noveltech

#### RandyG

·
##### Registered
Joined
·
7,762 Posts
I was holding back, cause I know nothing of Quattro, but then realised that I didn't have to know about it!! What do you want if it does not contain MO1?

Any way, here's what I have, and it seems to work

=IF((A6011:A621)="MO1",SUM(J601:L621)," ")

The total area will display nothing if it does not find MO1 in the first range. If you want it to something else, then replace the " "

#### noveltech

·
##### Registered
Joined
·
73 Posts
Discussion Starter · ·
Is it possible your formula has typo?

Does not work for me..

Thanks for the feedback...

If you find an answer...give me a post.

Thanks, NT

#### RandyG

·
##### Registered
Joined
·
7,762 Posts
=IF((A601:A621)="MO1",SUM(J601:L621)," ")

Yes, I had A6011,but has now been changed

#### noveltech

·
##### Registered
Joined
·
73 Posts
Discussion Starter · ·
=IF((A790:A791)="MO1",SUM(J790:L791)," ")
=IF((A790:A791)="MO1",SUM(J790:L791),"")

I have tried both above.

Result = #VALUE!

I am using the above formula.

If I do:

=SUMIF(A790:A791,"MO1",L790:L791)

Result = 356.76 which is correct.

However, the spreadsheet has other locations
where J and K is also being used to store numbers.

Therefore, sum of J thru L is the sum I need.

Will not work...

Thanks, NT

#### RandyG

·
##### Registered
Joined
·
7,762 Posts
OK, where are you putting the sum function? If I put it in any of the rows that are being looked into, the formula comes up correct.

If I try to put into any other rows, the formula produces an error.

So, I have MO1 in Colum A, Rows 790 to 792.
In row 790, I have 670 in J, 18 in K, and 4 in L
In Row 791, I have 75 in J, and nothing in the other columns
In Row 792, I have 20 in K, and nothing in the other columns

If I put the formula =IF((A790:A792)="MO1",SUM(J790:L792)," ") in any of the activbe rows (790 to 792) then the formula gives me the correct addition of all numbers, 787. If I put it in any other rows, I get ~VALUE listed.

Also, If I change the value in A791 to something other than MO1, the formula still adds works as above, unless you have it in row 791, then it displays blank, as per the IF statement.

Basically, I would need to understand what you were trying to do with this function before I could go any further.

If you like, you can email me an example of what you are doing.

#### noveltech

·
##### Registered
Joined
·
73 Posts
Discussion Starter · ·
Strange problem...

Your formula works fine in test sheet.

A1 thru z 10

Must be my cpu or software excel problem
at 790-791 table.

I no longer believe it's a formula issue.

Might be mem issue?

Thanks, nt

1 - 9 of 9 Posts
Status
Not open for further replies.