Formula which doesn’t work if you have Microsoft Office Excel 2003

G

Gennaro

I have entered the formula onto my spreadsheet. However it doesn’t work if
you have Microsoft Office Excel 2003!


000001 00001001 CLUB CASH =SUM((A2:A132="000087")*(B2:B132="000088")*D2:D132)
000001 00001002 CASH
000002 00012000 Bank -83225.26
000002 00013000 ?????? 621
000002 00014000 CASH ACCOUNT 2400
000005 00000004 HARJINDER CHANDAL 320



Any other formula to sort out my problem?

Thank you

G
 
D

Dave Peterson

Are those values really text? That's what you're comparing them to with
"000087".

And did you remember to hit ctrl-shift-enter since it is an array formula.
 
E

Earl Kiosterud

Gennaro,

First of all, your formula must be entered as an array formula, by pressing Ctrl-Shift-Enter
any time it's been edited, not just Enter.

Your formula must not be anywhere in rows 2:132, or it will look only in the row in which
the formula lives.

The values in columns A and B must be text values. 87 or 88 won't do, even if formatted for
the leading zeroes, e.g.: 000087.
--
Earl Kiosterud
www.smokeylake.com

Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...
 
J

JE McGimpsey

What do you mean by "doesn't work"?

If you array enter it (CTRL-SHIFT-ENTER), it should work fine in all XL
versions as long as all the values in column A and B are text (since you
use quotation marks) and Column D is numeric.
 
E

Earl Kiosterud

This is a weird one, guys. Gennaro sent me his workbook. He gets #VALUE! in his formula.
Cell D126 appears empty, yet if I press Delete, the formula begins to work. =Len(D126)
yields 0. Number formatting is General. That row doesn't happen to have 000087 and 000088,
but none does actually.

His values in columns A and B all indicate number stored as text. They're left justified,
yet the number formatting is General, Alignment is General, and the data appears
left-aligned, like it's text. No leading apostrophes. Probably imported. The problem
doesn't seem to relate to columns A or B. he didn't have any rows with 000087 and 000088,
but when I typed some in, the formula works -- as long as D126 has been cleared with the
Delete key. I made up another formula, like his, to look for stuff he does actually have in
columns A and B of some rows, and it worked -- but D126 had to have been deleted, or same
problem.

If I copy/paste D126 to D125, then both have to have been cleared for the formula to not
produce the error. I smell importing weirdness. D126 definitely stinks. I tried to look
at some other stuff, but then Excel crashed. I'm waiting for it to recover now.

Twilight Zone theme music plays now.
--
Earl Kiosterud
www.smokeylake.com

Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...
 
Top