sumproduct...issue

[bluepenink]

Hello

Having some trouble with my logic in the sumproduct, getting a #NUM error.

=SUMPRODUCT(--('[YTD_Branch SE v1.xls]Calgary-Saskatoon'!$D:$D=$B$56)*--('[YTD_Branch SE v1.xls]Calgary-Saskatoon'!$E$6:$Y$6=C$1)*--('[YTD_Branch SE v1.xls]Calgary-Saskatoon'!$C:$C=$A$2)*'[YTD_Branch SE v1.xls]Calgary-Saskatoon'!$E:$E)

Criteria:
$D:$D = city names (i.e. Calgary, New York, LA etc)
B56 = City name i.e. Calgary (one that I want)

E6:Y6 = heading name (i.e. cold calls, total calls etc)
C1 = heading i want (i.e. cold calls - only)

C:C = time frame i.e. Jan1, Jan2, Jan3
A2 = Jan3

E:E = where cold calls data is stored, well it starts in E9:E1119

I need to make this scroll across, pls help!!

thx you!

 

[MrKowz]

You can't use the double unary operator and the * in the same sumproduct. Also, your arrays must be of identical size. If you are in Excel 2003 or older, you can't use entire column references either. Give me a moment to figure out an alternative formula.

 

[MrKowz]

Try:

=SUMPRODUCT(--('[YTD_Branch SE v1.xls]Calgary-Saskatoon'!$D$9:$D$1119=$B$56),--('[YTD_Branch SE v1.xls]Calgary-Saskatoon'!$C$9:$C$1119=$A$2),INDEX('[YTD_Branch SE v1.xls]Calgary-Saskatoon'!$E$9:$Y$1119,MATCH(C$1,'[YTD_Branch SE v1.xls]Calgary-Saskatoon'!$E$6:$Y$6,0)))

 

[bluepenink]

thxs bud

im going to see if that will work out....will msg back asap

 

[Aladin Akyurek]

Hello

Having some trouble with my logic in the sumproduct, getting a #NUM error.

=SUMPRODUCT(--('[YTD_Branch SE v1.xls]Calgary-Saskatoon'!$D:$D=$B$56)*--('[YTD_Branch SE v1.xls]Calgary-Saskatoon'!$E$6:$Y$6=C$1)*--('[YTD_Branch SE v1.xls]Calgary-Saskatoon'!$C:$C=$A$2)*'[YTD_Branch SE v1.xls]Calgary-Saskatoon'!$E:$E)

Criteria:
$D:$D = city names (i.e. Calgary, New York, LA etc)
B56 = City name i.e. Calgary (one that I want)

E6:Y6 = heading name (i.e. cold calls, total calls etc)
C1 = heading i want (i.e. cold calls - only)

C:C = time frame i.e. Jan1, Jan2, Jan3
A2 = Jan3

E:E = where cold calls data is stored, well it starts in E9:E1119

I need to make this scroll across, pls help!!

thx you!

Looks like:

=SUMPRODUCT(
    --('[YTD_Branch SE v1.xls]Calgary-Saskatoon'!$D$19:$D$1119=$B$56),
    --('[YTD_Branch SE v1.xls]Calgary-Saskatoon'!$C:$C=$A$2),
        INDEX('[YTD_Branch SE v1.xls]Calgary-Saskatoon'!$E$9:$Y$1119,0,
          MATCH(C$1,'[YTD_Branch SE v1.xls]Calgary-Saskatoon'!$E$6:$Y$6,0)))

Does this meet your intent?

By the way, try not mix * and -- for coercing.

 

[bluepenink]

hello guys

i really appreciate the prompt response/help!

i have tried to follow through but getting some REF error. unfortunately, the firewall at my work wont let me upload files anywhere, so i had to upload to another forum. If you can kindly help, it would b grateful appreciated. the sample can be found below.

http://www.excelforum.com/excel-general/783126-sumproduct-problem.html#post2558295

again, much appreciated!

 

[Aladin Akyurek]

hello guys

i really appreciate the prompt response/help!

i have tried to follow through but getting some REF error. unfortunately, the firewall at my work wont let me upload files anywhere, so i had to upload to another forum. If you can kindly help, it would b grateful appreciated. the sample can be found below.

http://www.excelforum.com/excel-general/783126-sumproduct-problem.html#post2558295

again, much appreciated!

That requires registering. What does this yield?...

=SUMPRODUCT(
    --('[YTD_Branch SE v1.xls]Calgary-Saskatoon'!$D$19:$D$1119=$B$56),
    --('[YTD_Branch SE v1.xls]Calgary-Saskatoon'!$C$19:$C$1119=$A$2),
        INDEX('[YTD_Branch SE v1.xls]Calgary-Saskatoon'!$E$9:$Y$1119,0,
          MATCH(C$1,'[YTD_Branch SE v1.xls]Calgary-Saskatoon'!$E$6:$Y$6,0)))

Doesn't C1 just occur once in E6:Y6?

 

[bluepenink]

hello

i re-jigged the formula and still getting the same results

=SUMPRODUCT(--('Calgary-Saskatoon'!$D$8:$D$1119=$B$56),--('Calgary-Saskatoon'!$C$8:$C$1119=$A$2),INDEX('Calgary-Saskatoon'!$E$8:$E$1119,0,MATCH(D$1,'Calgary-Saskatoon'!$E$5:$Y$5,0)))

and yes, C1 or D1 for instance does work once only...any idea? thx you very much!!!

 

[bluepenink]

hello

this appears to do the trick!

=SUMPRODUCT(--('Nashville-Memphis'!$D$8:$D$1119=$D$56),--('Nashville-Memphis'!$B$8:$B$1119=$A2),INDEX('Nashville-Memphis'!$E$8:$Y$1119,0,MATCH(C$1,'Nashville-Memphis'!$E$5:$Y$5,0)))


thx you guys for your help!!!!!
<!-- END TEMPLATE: bbcode_code -->

 

[Aladin Akyurek]

hello

this appears to do the trick!

=SUMPRODUCT(--('Nashville-Memphis'!$D$8:$D$1119=$D$56),--('Nashville-Memphis'!$B$8:$B$1119=$A2),INDEX('Nashville-Memphis'!$E$8:$Y$1119,0,MATCH(C$1,'Nashville-Memphis'!$E$5:$Y$5,0)))


thx you guys for your help!!!!!
<!-- END TEMPLATE: bbcode_code -->

You are welcome. Thanks for providing us feedback.