Using Sumproduct with a concatenate formula

[Missidee]

I am having a very difficult time with this formula. I haven't used sumproduct before but because I am going to be pulling a lot of data from many sheets that many users are going to be accessing. Here is my sumif formula:

=SUMIF('[Scorecard 32 & VS & DWO.xls]Code 32'!$F:$F,CONCATENATE(B6,"ST"),'[Scorecard 32 & VS & DWO.xls]Code 32'!$B:$B)


I tried this sumproduct formula after reading the boards, but I get an error:

=SUMPRODUCT('[Scorecard 32 & VS & DWO.xls]Code 32'!$F1:$F50000=CONCATENATE(B5,"ST"),'[Scorecard 32 & VS & DWO.xls]Code 32'!$B1:$B50000)

Any help would be greatly appreciated!!!

 

[wigi]

Try...

=SUMPRODUCT(('[Scorecard 32 & VS & DWO.xls]Code 32'!$F1:$F50000=CONCATENATE(B5,"ST"))*('[Scorecard 32 & VS & DWO.xls]Code 32'!$B1:$B50000))

 

[Missidee]

Wigi,

I tried that formula and now I get a #VALUE error. Any other ideas?

Although...I tried dragging the formula down to the rest of the row...the first three return #Value errors, even though I know they should populate with values, the rest of the row populates correctly. What could be causing that?

 

[wigi]

The rows in the formula are not fixed, 1 and 50000, so most probably you will need to use $ signs to have absolute cell references.

 

[Missidee]

I noticed that too and inserted the $. When I do that and copy it down, all cells return the error #VALUE. Here is the formula:

=SUMPRODUCT(('G:\FSCRM\Midwest\Scorecard\[Scorecard 32 & VS & DWO.xls]Code 32'!$F$1:$F$50000=CONCATENATE(B5,"ST"))*('G:\FSCRM\Midwest\Scorecard\[Scorecard 32 & VS & DWO.xls]Code 32'!$B$1:$B$50000))

Thanks

 

[wigi]

Limit the 50000 to let's say 10, and test the formula in this way. After that, you can still increase the range.