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
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
Last edited by Killer17; 10-16-2008 at 12:32 AM.
The ranges need to be the same size.
Entia non sunt multiplicanda sine necessitate
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?
"W1" represents a worksheetIn "W*1" does * represent a wildcard or a literal *? ?
This is the Formula i'm using in Cell D15:D199What sort of values do you have in D15:D199?
=IF(AND(D14<>0,D30<>""),IF(D14>D30,"W","L"),"")
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") )
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
Assuming the formula I posted doesn't do what you want, I think you need to post a workbook and explain in context.
Where does the formula go? Please explain your workbook.
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.
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Thanks NBVC
The two formulas work fine thanks a million
Last edited by NBVC; 10-16-2008 at 12:35 AM. Reason: Incorrect spelling of my name
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks