What is wrong with this formula?

=SUM(IF(('Batch Log'!A2:A2001="February")*('Batch
Log'!G2:G2001="530204-03")*('Batch Log'!H2:H2001="3000272.02"),'Batch
Log'!F2:F2001,0))

It will compute successfully with just the first two conditions, but cannot
when the third condition is added.

On Jan 6, 9:34*am, Griffey5
wrote:
What is wrong with this formula?

=SUM(IF(('Batch Log'!A2:A2001="February")*('Batch
Log'!G2:G2001="530204-03")*('Batch Log'!H2:H2001="3000272.02"),
'Batch Log'!F2:F2001,0))

It will compute successfully with just the first two conditions, but
cannot when the third condition is added.

Look at the format of H2:H2001. Is truly text? I can only guess that
perhaps that 3rd term should have 3000272.02 instead of "3000272.02".
Alternatively, perhaps you intended H2:H2001 to be formatted as text,
but it is not.

Having said that, I should add that I think it is a bad idea to test
for equality of a non-integral value (unless it is text). Even if you
form 3000272.02 from integers (e.g. 1000*A1+mod(A2,1000)+mod(A3,100)/
100), it is not exactly what it appears to be.

Finally, if my guesses prove to be incorrect, it would be prudent for
you to explain what you mean when you say the formula "cannot
compute". Do you get an Excel error, as your subject line suggests?
If so, what error? Or do you simply get an unexpected result, as the
phrases "cannot compute" suggests to me? If so, what do you get, and
what do you expect instead?

HTH.

See the other group where you posted the same question.

Please don't multi-post; cross-post if you need to.
http://www.cs.tut.fi/~jkorpela/usenet/xpost.html
--
David Biddulph

"Griffey5" wrote in message
...
What is wrong with this formula?

=SUM(IF(('Batch Log'!A2:A2001="February")*('Batch
Log'!G2:G2001="530204-03")*('Batch Log'!H2:H2001="3000272.02"),'Batch
Log'!F2:F2001,0))

It will compute successfully with just the first two conditions, but
cannot
when the third condition is added.

Perhaps the values in Column H are true XL numbers, and *not* text!

Your parenthesis placed around "3000272.02" are telling the formula to look
for *text* values.

It they're numbers, just remove the parens.

And of course, you *do know* that your formula is an *array* formula, and
must be entered using CSE.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Griffey5" wrote in message
...
What is wrong with this formula?

=SUM(IF(('Batch Log'!A2:A2001="February")*('Batch
Log'!G2:G2001="530204-03")*('Batch Log'!H2:H2001="3000272.02"),'Batch
Log'!F2:F2001,0))

It will compute successfully with just the first two conditions, but
cannot
when the third condition is added.

On Jan 6, 9:54*am, "David Biddulph" <groups [at] biddulph.org.uk
wrote:
See the other group where you posted the same question.

Can you provide a link? Alternatively, date, subject and author
information?

Using Google Groups search for author "griffey5", the only other
similar posting that I could find was under m.p.e.crashesgpfs(!). And
I found no responses there. (No surprise!) However, I know that not
everything posted to MS "discussion groups" find their way into Google
Groups (sigh).

On Jan 6, 9:59*am, "Ragdyer" wrote:
Your parenthesis placed around "3000272.02" are telling the formula to look
for *text* values.

It they're numbers, just remove the parens.

I think you mean quotes or double-quotes.

And of course, you *do know* that your formula is an *array* formula,
and must be entered using CSE.

Good point. But if the OP does not understand how to work with array
formula, it's a good bet that the OP does not know that CSE means ctrl
+shift+Enter.

To the OP: if that might be your problem, select the cell with the
formula, press F2, then press ctrl+shift+Enter.

Since I've already finished my Sunday morning pot of coffee, there's really
*no* excuse for making that mistake ... except ... of course ... old
age!<bg
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"joeu2004" wrote in message
...
On Jan 6, 9:59 am, "Ragdyer" wrote:
Your parenthesis placed around "3000272.02" are telling the formula to
look
for *text* values.

It they're numbers, just remove the parens.

I think you mean quotes or double-quotes.

And of course, you *do know* that your formula is an *array* formula,
and must be entered using CSE.

Good point. But if the OP does not understand how to work with array
formula, it's a good bet that the OP does not know that CSE means ctrl
+shift+Enter.

To the OP: if that might be your problem, select the cell with the
formula, press F2, then press ctrl+shift+Enter.

It looks like he posted it earlier today in a newsgroup named...

microsoft.public.excel.crashesgpfs

(which I never heard of before) under the Subject line "Arrays in Excel",
but Google didn't show him getting an answer there that I can see.

Rick


"joeu2004" wrote in message
...
On Jan 6, 9:54 am, "David Biddulph" <groups [at] biddulph.org.uk
wrote:
See the other group where you posted the same question.

Can you provide a link? Alternatively, date, subject and author
information?

Using Google Groups search for author "griffey5", the only other
similar posting that I could find was under m.p.e.crashesgpfs(!). And
I found no responses there. (No surprise!) However, I know that not
everything posted to MS "discussion groups" find their way into Google
Groups (sigh).

It's in the .misc group, with a single response from David.
"Excel Formula Errors"
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Rick Rothstein (MVP - VB)" wrote in
message ...
It looks like he posted it earlier today in a newsgroup named...

microsoft.public.excel.crashesgpfs

(which I never heard of before) under the Subject line "Arrays in Excel",
but Google didn't show him getting an answer there that I can see.

Rick


"joeu2004" wrote in message
...
On Jan 6, 9:54 am, "David Biddulph" <groups [at] biddulph.org.uk
wrote:
See the other group where you posted the same question.

Can you provide a link? Alternatively, date, subject and author
information?

Using Google Groups search for author "griffey5", the only other
similar posting that I could find was under m.p.e.crashesgpfs(!). And
I found no responses there. (No surprise!) However, I know that not
everything posted to MS "discussion groups" find their way into Google
Groups (sigh).

The place where I'd seen the question was in a somewhat less obscure group
(microsoft.public.excel.misc).

The posting was
http://groups.google.co.uk/group/mic...174ed6aa75cd82
http://preview.tinyurl.com/yqgm8q
--
David Biddulph

"Rick Rothstein (MVP - VB)" wrote in
message ...
It looks like he posted it earlier today in a newsgroup named...

microsoft.public.excel.crashesgpfs

(which I never heard of before) under the Subject line "Arrays in Excel",
but Google didn't show him getting an answer there that I can see.

Rick


"joeu2004" wrote in message
...
On Jan 6, 9:54 am, "David Biddulph" <groups [at] biddulph.org.uk
wrote:
See the other group where you posted the same question.

Can you provide a link? Alternatively, date, subject and author
information?

Using Google Groups search for author "griffey5", the only other
similar posting that I could find was under m.p.e.crashesgpfs(!). And
I found no responses there. (No surprise!) However, I know that not
everything posted to MS "discussion groups" find their way into Google
Groups (sigh).

I looked physically looked there (as its one of the groups I monitor) and
didn't see it, so I did a Google search which didn't show the misc group
either... instead, it showed the group I posted. I'm not sure why I missed
it in the misc group (it's there now), but didn't Google used to be much
quicker about getting things in its archive than it seems to do now?

Rick


"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
The place where I'd seen the question was in a somewhat less obscure group
(microsoft.public.excel.misc).

The posting was
http://groups.google.co.uk/group/mic...174ed6aa75cd82
http://preview.tinyurl.com/yqgm8q
--
David Biddulph

"Rick Rothstein (MVP - VB)" wrote in
message ...
It looks like he posted it earlier today in a newsgroup named...

microsoft.public.excel.crashesgpfs

(which I never heard of before) under the Subject line "Arrays in Excel",
but Google didn't show him getting an answer there that I can see.

Rick


"joeu2004" wrote in message
...
On Jan 6, 9:54 am, "David Biddulph" <groups [at] biddulph.org.uk
wrote:
See the other group where you posted the same question.

Can you provide a link? Alternatively, date, subject and author
information?

Using Google Groups search for author "griffey5", the only other
similar posting that I could find was under m.p.e.crashesgpfs(!). And
I found no responses there. (No surprise!) However, I know that not
everything posted to MS "discussion groups" find their way into Google
Groups (sigh).

On Jan 6, 11:15*am, "Rick Rothstein \(MVP - VB\)"
wrote:
I looked physically looked there (as its one of the groups I monitor) and
didn't see it, so I did a Google search which didn't show the misc group
either... instead, it showed the group I posted. I'm not sure why I missed
it in the misc group (it's there now), but didn't Google used to be much
quicker about getting things in its archive than it seems to do now?

Perhaps. But I don't think it's a question of how quickly Google
Groups gets things into its archive. I have used GG for a few years
now, and lately, it seems to have a lot of consistency problems --
articles not found in searches; articles found in the index, but not
readable; articles that come and go. For newly posted articles, some
of those anomalies could be explained as internal servers being out of
sync. But I suspect the more general explanation is that GG is either
having trouble with its in internal network of servers, or its
networked application have become less tolerant of errors or delays.

That conclusion is consistent with another observation that is
happening more often: articles posted in GG do not appear in GG,
sometimes ever, but usually not for a very long time. Yet they do
appear (sometimes) first in MS Discussion Groups. I believe that can
only be explained by problems in GG's internal network, being it
slowness or unreliability.

BTW, there has always been a problem with GG seeing articles that were
originally posted in MS Discussion Groups -- at least, the base
article of a thread. Frequently, I never see the base article in GG,
only the replies; but some base articles do come through. Moreover,
GG usually shows replies that were posted in MSDG; I can only remember
seeing problems with base articles. I don't know if GG or MSDG is at
fault.

Tried removing the parenthesis and it did not work as a number. Also tried
changing it to text so it would read it as text, did not work. I even tried
changing the number to 3000272-02 so it would read as text, did not work.

Yes I know this is an array.

"Ragdyer" wrote:

Perhaps the values in Column H are true XL numbers, and *not* text!

Your parenthesis placed around "3000272.02" are telling the formula to look
for *text* values.

It they're numbers, just remove the parens.

And of course, you *do know* that your formula is an *array* formula, and
must be entered using CSE.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Griffey5" wrote in message
...
What is wrong with this formula?

=SUM(IF(('Batch Log'!A2:A2001="February")*('Batch
Log'!G2:G2001="530204-03")*('Batch Log'!H2:H2001="3000272.02"),'Batch
Log'!F2:F2001,0))

It will compute successfully with just the first two conditions, but
cannot
when the third condition is added.

Try with Sumproduct.
=SUMproduct(--('Batch Log'!A2:A2001="February")*--('Batch
Log'!G2:G2001="530204-03")*--('Batch Log'!H2:H2001="3000272.02"),'Batch
Log'!F2:F2001,0))
With regards
Sreedhar
"Griffey5" wrote:

What is wrong with this formula?

=SUM(IF(('Batch Log'!A2:A2001="February")*('Batch
Log'!G2:G2001="530204-03")*('Batch Log'!H2:H2001="3000272.02"),'Batch
Log'!F2:F2001,0))

It will compute successfully with just the first two conditions, but cannot
when the third condition is added.

Perhaps you've got spaces or other invisible characters in your text string.
For one of the column H cells where you think you've got "3000272.02", does
=ISTEXT(H2) return TRUE, and does =LEN(H2) return 10 ?
--
David Biddulph

"Griffey5" wrote in message
...
Tried removing the parenthesis and it did not work as a number. Also
tried
changing it to text so it would read it as text, did not work. I even
tried
changing the number to 3000272-02 so it would read as text, did not work.

Yes I know this is an array.

"Ragdyer" wrote:

Perhaps the values in Column H are true XL numbers, and *not* text!

Your parenthesis placed around "3000272.02" are telling the formula to
look
for *text* values.

It they're numbers, just remove the parens.

And of course, you *do know* that your formula is an *array* formula, and
must be entered using CSE.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Griffey5" wrote in message
...
What is wrong with this formula?

=SUM(IF(('Batch Log'!A2:A2001="February")*('Batch
Log'!G2:G2001="530204-03")*('Batch Log'!H2:H2001="3000272.02"),'Batch
Log'!F2:F2001,0))

It will compute successfully with just the first two conditions, but
cannot
when the third condition is added.

I don't think you need the double unary minus where you're doing a multiply
operation with *
--
David Biddulph

"yshridhar" wrote in message
...
Try with Sumproduct.
=SUMproduct(--('Batch Log'!A2:A2001="February")*--('Batch
Log'!G2:G2001="530204-03")*--('Batch Log'!H2:H2001="3000272.02"),'Batch
Log'!F2:F2001,0))
With regards
Sreedhar
"Griffey5" wrote:

What is wrong with this formula?

=SUM(IF(('Batch Log'!A2:A2001="February")*('Batch
Log'!G2:G2001="530204-03")*('Batch Log'!H2:H2001="3000272.02"),'Batch
Log'!F2:F2001,0))

It will compute successfully with just the first two conditions, but
cannot
when the third condition is added.