SUMPRODUCT HELP
 

Hello,
I have the following formula:

=SUMPRODUCT(--(A4='[RRBB804 wKLY 011505.xls]RRBB804 wKLY
011505'!$A$2:$A$1332),--($C$2='[RRBB804 wKLY 011505.xls]RRBB804 wKLY
011505'!$C$2:$C$1332),--('[RRBB804 wKLY 011505.xls]RRBB804 wKLY
011505'!$E$2:$E$1332))

If I take out either of the first two conditions, I will receive a total. I
can't get it to work looking for both conditions.

A4 = AAbb123
A2:A1332 = various alpha/numeric including AAbb123

$C$2 = ABCDE
C1:C1332 = various alpha/numeric including ABCDE

E2:E1332 = numbers

Any help is appreciated.
Thanks,
John

... Max's suggestion will make no difference.

Of course it will <g, if there were any whitespaces somewhere
throwing the matching off
(which would result in either wrong, lower totals or even zeroes)

BTW, the final -- is unnecessary

Think it would make a difference
if there were any text numbers in $E$2:$E$1332
(thought it okay to leave this as-is)

----

Btw, Bob, would like to express my deep sympathies on the sudden and tragic
loss of Frank. I, too share the deep anguish felt at his loss, and really
treasured the moments spent exchanging views and banter with him only very
recently. Thought it was a fine and fitting tribute that Norman gave and
which you've also put up on your site.

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

"Bob Phillips" wrote:

When you say it doesn't work, do you get an error, or just returns 0.
Because you say it works okay with either of one conditions, I suspect that
Max's suggestion will make no difference.

Are you sure that there are any rows that match both criteria, your formula
is an AND condition not OR.

BTW, the final -- is unnecessary

=SUMPRODUCT(--(A4='[RRBB804 wKLY 011505.xls]RRBB804 wKLY
011505'!$A$2:$A$1332),--($C$2='[RRBB804 wKLY 011505.xls]RRBB804 wKLY
011505'!$C$2:$C$1332),'[RRBB804 wKLY 011505.xls]RRBB804 wKLY
011505'!$E$2:$E$1332)

I would suggest that you start from scratch, and create a simple couple of
ranges, and build it up step by step with test data, get familiar with it.

--

HTH

RP
(remove nothere from the email address if mailing direct)

Thank you Max and Bob.
I carefully went thru the formula, found my error and it works fine.


"Max" wrote in message
...
... Max's suggestion will make no difference.

Of course it will <g, if there were any whitespaces somewhere
throwing the matching off
(which would result in either wrong, lower totals or even zeroes)

BTW, the final -- is unnecessary

Think it would make a difference
if there were any text numbers in $E$2:$E$1332
(thought it okay to leave this as-is)

----

Btw, Bob, would like to express my deep sympathies on the sudden and
tragic
loss of Frank. I, too share the deep anguish felt at his loss, and really
treasured the moments spent exchanging views and banter with him only very
recently. Thought it was a fine and fitting tribute that Norman gave and
which you've also put up on your site.

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

"Bob Phillips" wrote:

When you say it doesn't work, do you get an error, or just returns 0.
Because you say it works okay with either of one conditions, I suspect
that
Max's suggestion will make no difference.

Are you sure that there are any rows that match both criteria, your
formula
is an AND condition not OR.

BTW, the final -- is unnecessary

=SUMPRODUCT(--(A4='[RRBB804 wKLY 011505.xls]RRBB804 wKLY
011505'!$A$2:$A$1332),--($C$2='[RRBB804 wKLY 011505.xls]RRBB804 wKLY
011505'!$C$2:$C$1332),'[RRBB804 wKLY 011505.xls]RRBB804 wKLY
011505'!$E$2:$E$1332)

I would suggest that you start from scratch, and create a simple couple
of
ranges, and build it up step by step with test data, get familiar with
it.

--

HTH

RP
(remove nothere from the email address if mailing direct)

You're welcome !
Glad to hear you sorted it out ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"JHL" wrote in message
om...
Thank you Max and Bob.
I carefully went thru the formula, found my error and it works fine.