SUMPRODUCT Error

**Killer17** · 10-15-2008, 07:22 PM

I'm trying to use this formula but I keep getting an #value error

=SUMPRODUCT(--('[Weekly Sheets.xlsx]W1'!$B$5:$B$199=C1),--'[Weekly Sheets.xlsx]W1'!$D$15:$D$199="W*1")

If worksheet W1 in cell D15 has a "W" looking for a result of 1

Thanks

**shg** · 10-15-2008, 07:23 PM

The ranges need to be the same size.

**daddylonglegs** · 10-15-2008, 07:29 PM

In "W*1" does * represent a wildcard or a literal *?

If it's the former then your formula won't do what you want - even with same-sized ranges - [you can't use wildcards with a comparison operator like =]

What sort of values do you have in D15:D199?

**Killer17** · 10-15-2008, 08:05 PM

In "W*1" does * represent a wildcard or a literal *? ?

"W1" represents a worksheet

What sort of values do you have in D15:D199?

This is the Formula i'm using in Cell D15:D199

=IF(AND(D14<>0,D30<>""),IF(D14>D30,"W","L"),"")

**shg** · 10-15-2008, 08:11 PM

DLL was referring to the W*1 in '[Weekly Sheets.xlsx]W1'!$D$15:$D$199="W*1"

If you're just looking for W, then

=SUMPRODUCT( ('[Weekly Sheets.xlsx]W1'!$B$15:$B$199=C1) * ('[Weekly Sheets.xlsx]W1'!$D$15:$D$199="W") )

**Killer17** · 10-15-2008, 08:13 PM

Here an example of what I'm trying to do

I have two Work Sheets one is Team Sheet other named "W1"

On Team Sheet Cell C1 is the Team name
I'm looking for the result “W1” worksheet into my Team Sheet

On my Worksheet “W1” Names are listed from B5 down to B199.

The W are located in cell D15 to D199 if there's a cell with a "W" in it must match Team name and return a result in my Team Sheet in Cell B21 the result should be based on “W” =1

I hope this helps