Recap of Number of word in a different Cell

Question

In my sheet "Download".

In Column C I have:

BRONZE
SILVER
SILVER
BRONZE
GOLD
PLATIN
PLPLUS
AMBASS
PLPLUS 
etc...

What I Would Like to do is

in the cell J7: Bronze: "Total of Bronze"  
in the cell J8: Silver: "Total of Silver"  
in the cell J9: Gold: "Total of Gold"  
in the cell J10:Platinum "Total of PLATIN" 
in the cell J11:Platinum Plus "Total of PLPLUS" 
in the cell J12:Ambassador "Total of AMBASS" 
in the cell J13:Total "Total of Bronze, silver, gold, platin, plplus & Ambass"

I don't think for someone who knows well vba it will be difficult to answer.

`Countif()` and/or `Countifs()`? What do you mean by "Total", what are you asking for? Can you please clarify what you're looking to do, and what you've tried already? What does this have to do with "user-experience"? — BruceWayne, Mar 07 '17 at 22:32
@BruceWayne. First of all I thought it was clear in my question. In column C I have different Status: Bronze, silver, gold etc... Then I want to put on the same sheet but in range J7 the total of cell written Bronze. I want on J7 written " Bronze:" and then the number of cell with bronze... same in J8 but with silver, J9 with gold etc.. and in J13 the SUM of all of them — JohanEs, Mar 07 '17 at 22:55
@BruceWayne, I put user experience because I tought you needed experience to find the solution... — JohanEs, Mar 07 '17 at 22:56
In which row in column C does your list start? Also please show whatever code you already have and describe what is wrong with it - error codes, unexpected outputs. — Mark Fitzgerald, Mar 07 '17 at 23:28
@MarkFitzgerald, It start in C2 until there is no more word.. every week it changes so it need to be automatic. In column A I have Room Number, In column B I have the name of the Guest, In column C I have the status (Bronze, Silver, Gold etc...) So I need to know how many guest are Bronze etc.... I want to report the result of each statut on J7, J8 etc.. but not only the number.. As well what status is for this Total.... — JohanEs, Mar 08 '17 at 00:37

score 1 · Accepted Answer · answered Mar 08 '17 at 00:35

1

For Excel Formula Try this on J1

="Total of " & COUNTIFS(C:C,C1) & " " & C1

answered Mar 08 '17 at 00:35

0m3r

12,286
15
35
71

1

In excel Formula, I know how to do it... but it is on vba that is more difficult... – JohanEs Mar 08 '17 at 00:46
@JohanEs `ThisWorkbook.Worksheets("Sheet1").Range("J1").Formula = "=COUNTIFS(C:C,C2)"` – 0m3r Mar 08 '17 at 00:59
1

How do we put a Name in front of the Result ? it can be in the Cell Before. For Example : ThisWorkbook.Worksheets("Sheet1").Range("J7").Formula = "=COUNTIFS(C:C,""BRONZE"")" this will give me a number but in front of this result I want the Word Bronze or in the cell before which is I7 in this case – JohanEs Mar 08 '17 at 01:08

score 1 · Answer 2 · answered Mar 08 '17 at 01:12

1

I am not sure why you are doing this in VBA, but because you insist it needs to be:

Sub CountThem
    With Worksheets("Download")
        .Range("J7").Value = "Bronze: " & Application.CountIf(.Range("C:C"), "Bronze")
        .Range("J8").Value = "Silver: " & Application.CountIf(.Range("C:C"), "Silver")
        .Range("J9").Value = "Gold: " & Application.CountIf(.Range("C:C"), "Gold")
        .Range("J10").Value = "Platinum: " & Application.CountIf(.Range("C:C"), "PLATIN")
        .Range("J11").Value = "Platinum Plus: " & Application.CountIf(.Range("C:C"), "PLPLUS")
        .Range("J12").Value = "Ambassador: " & Application.CountIf(.Range("C:C"), "AMBASS")
        .Range("J13").Value = "Total: " & _
                              (Application.CountIf(.Range("C:C"), "Bronze") + _
                               Application.CountIf(.Range("C:C"), "Silver") + _
                               Application.CountIf(.Range("C:C"), "Gold") + _
                               Application.CountIf(.Range("C:C"), "PLATIN") + _
                               Application.CountIf(.Range("C:C"), "PLPLUS") + _
                               Application.CountIf(.Range("C:C"), "AMBASS"))

    End With
End Sub

answered Mar 08 '17 at 01:12

YowE3K

23,852
7
26
40

THANK YOU !!! I just to figure it out, How to change color each cell to make it Design... lol – JohanEs Mar 08 '17 at 01:32
@JohanEs Record a macro setting the colours manually. Then look at that code and find what colour numbers it used. Then modify the code in my answer by adding extra lines to say things like `.Range("J7").Interior.Color = 49407` etc. – YowE3K Mar 08 '17 at 01:37
If I want to change the font Color: I can put : Sheets("Download").Select Range("J7:J14").Cell.Font.Color = 2 I don't want something complicated just to change the Font in White. – JohanEs Mar 08 '17 at 01:50
@JohanEs There is no need to use `Select` - the two statements in your previous comment can be written as `Sheets("Download").Range("J7:J14").Font.Color = 2` (or, within the `With` block in my code, as `.Range("J7:J14").Font.Color = 2` - the leading `.` says to use the object of the `With`). Avoid `Select` and `Activate` as much as possible. See [this](http://stackoverflow.com/q/10714251/6535336). – YowE3K Mar 08 '17 at 02:19

Recap of Number of word in a different Cell

2 Answers2