I need help from you smart people.

Originally Posted by Pipestew

Hey - I am new to this forum and look forward to recieving answers to many questions in the future. I am fairly new to Excel, so some of my questions may be extremely simple, but any help will be appreciated.

What I am trying to do....

I have 2 columns that I am concerned with. Column 1 contains a list of numbers. Column 2 contains either "W" (win) or "L" (loss). I want to perform a count of how many times "W" and "L" appear, but I want the count to be specific to the number in column 1. Ex...If value of column 1 is "5", how many are "W" and how many are "L". I have experimented with all possible variations of the COUNTIF function, but have had no luck. I apologize if this is confusing or worded wrong. Thanks in advance for any replies.

Hi

try

=SUMPRODUCT(--(A$1:A$11=A1)*(--(B$1:B$11=B1)))


with your data length in place of '11'
and formula fill down the extent of your data.

Let me know how you go
---

Originally Posted by Pipestew

Thanks - That seems to have solved the problem....next question....

The spreadsheet is designed to keep track of wins, losses, and ties for sporting events. I want the total record to display in one cell(for each rating found in column 1)... Ex. " 7 - 3 - 1 "

My plan is as follows...

Using your formula, =SUMPRODUCT(--(G$4:G$73=G4)*(--(H$4:H$73=H4))), I will have a cell for each desired rating. To display the total record in one cell... Cell1&" - "&Cell2&" - "&Cell3. I of course can then hide the cells that contain that information.

Would you recommend another method, or have any variations to the above mentioned method.

HI,

I cannot see what you are trying to do with G & H - nor how you make the totals you are asking.

Would not the total 'wins' be the SumIf of all things 'W'
and the ties a SumIf of all things "T"
and the losses a SumIf of all things "L" ?


Or did you want a CountIf of all things "5" ?

---

Originally Posted by Pipestew

Sorry for the confusion. The formula you gave me was exactly what I needed. The last post is basically a separate question. I will not explain the H and G columns(unless you are personally interested) as it is not important to the next question.

Column S contains the following values (from S4 to S12), "1, 0, 0, 5, 0, 0, 19, 9, 1". The values were calculated using the formula you provided(with G and H as the range)

S4: How many times a value of 5 resulted in a win.
S5: How many times a value of 5 resulted in a loss.
S6: How many times a value of 5 resulted in a tie.
S7 - S9: Value of 3 W, L, T
S10 - S12: Value of 1 W, L, T

In a separate cell, I want to display the values of Column S in the form of a sports record ( W - L - T ). I want to display the record for each value (5, 3, 1). The records will read as follows....

(S4 - S5 - S6)....(S7 - S8 - S9)....(S10 - S11 - S12)


I tried using the formula, =S4&" - "&S5&" - "&S6, but had no success.

Hi,

the formula =S4&" - "&S5&" - "&S6 looks valid, what result did you get?

except in one of those cells of course
---

Originally Posted by Pipestew

Originally, the forumal itself was displaying in the cell, but then I changed the format from text to general and it works perfectly. Thanks for you help.

Good to see, and thanks for the response

---