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

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

Assuming the formula I posted doesn't do what you want, I think you need to post a workbook and explain in context.

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

Originally Posted by shg

Assuming the formula I posted doesn't do what you want, I think you need to post a workbook and explain in context.

Hey SHG it didn't work

so I posted an example

**shg** · 10-15-2008, 09:18 PM

Where does the formula go? Please explain your workbook.

**Killer17** · 10-15-2008, 09:27 PM

Originally Posted by shg

Where does the formula go? Please explain your workbook.

The forumla should go under each team sheet in cell D7 & E7 I have update the sheet to show you.

Work Sheet "W1" is where the W & L are listed for each team.

**NBVC** · 10-15-2008, 11:18 PM

If your tables in W1 sheet are consistently spaced and sized, then you can try something like this:

In D7:

=COUNTIF(OFFSET('W1'!B1,MATCH(C1,'W1'!B:B,0),,15,5),"W") for Wins

and in E7:

=COUNTIF(OFFSET('W1'!B1,MATCH(C1,'W1'!B:B,0),,15,5),"L") for Losses

I have allowed for 15 rows of data between Team names in W1 sheet. So that the W's and L's are contained within those 15 rows. The 5 allows for a width of 5 columns from column B in the W1 sheet.