Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I created a spreadsheet to track my sons wrestling team. The text I'm trying to convert is Wp, Wt, Wm, etc. The first letter stands for Win and the second letter represents how the match was won.

  • p = pin and is 6 team points
  • t = technical fall and is 5 team points
  • m = major decision and is 4 team points

I found this formula in a prior thread =SUM(COUNTIF(A1:G1,{"A","B","C"})*{1,2,3}) and it seems perfect. Here is the formula I came up with based of the previous; =SUM(COUNTIF(C3:C27,{"Wp","Wt","Wm","Wd","Wf","Wbd"})×{6,5,4,3,6,6}), but I'm getting "The formula contains a number outside the valid range" error.

How can I correct this?

share|improve this question
    
Is that x supposed to be a *? – Kyle Jan 14 at 18:13
1  
Your formula works for me. Provided the x is entered as *, it should work. This is an array formula. Have you used Ctrl+Shift+Enter to activate it so it will work? – CharlieRB Jan 14 at 18:25
    
It is supposed to be that symbol. I'm not sure how to type that. When I type asterisk it changes it to x. Thanks. – Jason Jan 15 at 22:47
    
I tested this =SUM(COUNTIF(D3:D27,{"Wp"})*{6}) this works. As soon as I add more values I get the error message the formula contains a number outside the range. – Jason Jan 19 at 16:09

As stated in the comments, your formula works for others once you replace the x with * (that may be a system difference but I can't figure it out). It even works without entering it as an array formula with Ctrl+Shift+Enter so that's not the problem, either.

If you're still having trouble, there are other ways to get at the same result. Here are a couple:

  1. Use a SUMPRODUCT formula instead. It looks fairly similar.

    =SUMPRODUCT(COUNTIF(C3:C27,{"Wp","Wt","Wm","Wd","Wf","Wbd"}),{6,5,4,3,6,6}

  2. Add a helper column that shows the score for each match. If you don't want a separate lookup table - it's generally best practice but may be overkill for your application - you can do it all in one formula. Copy / paste what's below and then copy it down for each match.

    =CHOOSE(MATCH(C3,{"Wp","Wt","Wm","Wd","Wf","Wbd"},0),6,5,4,3,6,6)

share|improve this answer
    
Thank you so much for your help. I ended up using multiple COUNTIF. It's very long but it works. SUM(COUNTIF(C3:C32,{"Wp*"})×{6})+(COUNTIF(C3:C32,{"Wt*"})×{5‌​})+(COUNTIF(C3:C32,{‌​"Wm*"})×{4})+(COUNTI‌​F(C3:C32,{"Wd*"})×{3‌​})+(COUNTIF(C3:C32,{‌​"Wbd*"})×{6}) – Jason Jan 26 at 22:48
    
BTW the x's in this long COUNTIF were entered as asterisk, go figure . I will try your suggestions. Thanks again. – Jason Jan 26 at 23:02

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .