SUMPRODUCT HELP

[JHL]

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]

Perhaps there's some white space(s) somewhere
which is throwing the matching off
(Don't think there's anything wrong with the posted formula)

Try wrapping TRIM(..) around in the 2 conditions
to make it more robust, viz. replace:

As 1st cond, with:
--(TRIM(A4)=TRIM('[RRBB804 wKLY 011505.xls]RRBB804 wKLY
011505'!$A$2:$A$1332))

As 2nd cond, with:
--(TRIM($C$2)=TRIM('[RRBB804 wKLY 011505.xls]RRBB804 wKLY
011505'!$C$2:$C$1332))

 

[Bob Phillips]

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)

 

[Max]

... 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 <at>yahoo<dot>com
----

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)

 

[JHL]

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

... 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 <at>yahoo<dot>com
----

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)

 

[Max]

You're welcome !
Glad to hear you sorted it out ..