Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
... 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) |
#3
|
|||
|
|||
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) |
#4
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Another Sumproduct & #N/A problem | Excel Worksheet Functions | |||
SUMPRODUCT | Excel Worksheet Functions | |||
Sumproduct ... Empty Cells vs Spaces? | Excel Discussion (Misc queries) | |||
Sumproduct function not working | Excel Worksheet Functions | |||
adding two sumproduct formulas together | Excel Worksheet Functions |