URGENT: Help with counting how often a value occurs

dmshott · Aug 28, 2015

Please help! How can I get a total number of each occurrence by state if my data currently looks like this?

ACO Service Area

AZ

AR

AZ, NM

AZ

AZ, TX

CA

CA, MO, TX

CA

CA, MS, OH

CA

CA, FL

CA

CA, DC, FL, GA, LA, NC, SC, TN, TX

CA

CA, PA, TN

CA, IN, MI

CA, IL, IN, IA

CA

OR, WA

CA

IN, OH

IN

CA

CO

CT, NY

CT

CT, NY

CT

CT, NY

DE, MD

NJ

GA, SC

AL, FL

FL

FL, SC, GA

FL

LA

FL

AL, MS, FL

FL, MO, NC, RI

FL

FL, NJ, PA

FL, TX

FL

MS

FL

GA

GA, SC

DC, MD

FL, GA

AL, GA

AL

GA, IN, OH

GA

FL, GA

GA

ID, OR

IL

IL, MA

IL

FL

IL

IL, IN

IL

IL, NC

IL

OH

IN, MI

IL, IN, KY

IN

IL, IN

IN

IN, MI

IL, IN

IN

IN, MI

IN

IN, MI

IL, IA

IA

IL, IA, WI

AR

AR, OK

KS

AR, OK

KS, MO

KS

KS, MO

KS

KY, LA, TN

IL, KY

KY

PA

IN, KY

KY

IN, KY

KY

IN, KY

TN

LA

LA, MS

LA, MS, TN

ME

ME, NH

MD

DE, MD, PA

AR, MA, NY

MD, PA

MD

KY, OH, NY, SC, VA

MD, PA

DC, MD

MD

DE, MD, VA

DE, MD

MD, WV, PA

DC, MD

MD

DC, MD

MD, PA, WV

VA

MD, PA, WV

CT, IL, IA, MA, NY, OH, PA

CT, MA

MA

MA, NH

MA

MA, NH

CA, MA, PA, TX, UT, WV, CT, IA

CT, MA

MA, RI

MA

MA, NH

MI

GA, MI

FL, MI, MO, OH, TX, VA, WI

MN

MN, ND, WI

MN, WI

MN

MS

AL, MS

GA, KY, MS, NC, TN, VA

IL, MO

MO

AR, CO, KY, MO, TN

KS, MO

AR, MO

AR, MO, OK

IL, MO

MT, WY

IA, NE

NE

NV

AZ, CA, NV, MO

CA, NV

NV

ME, MA, NH

NH, VT

NJ

NJ, PA

NJ

NJ, PA

NJ

NJ, NY

NJ

DE, NJ, PA

NJ

NM

NY

CT, NY

NY

NJ, NY

NY, PA

CT, NY

NY

NJ, NY

FL, NJ, NY

NY

CT, NY

NC

ND, SD

OH

KY, OH

OH

MI, OH

OH

KS, OK

OK

OR

NJ, PA

NY, PA

PA

NJ, PA

PA

MD, PA

PR

MA, RI

NC

SC

NC

NC, SC, VA

NC, SC

NC

SC

NC

TN, VA

TN

GA, TN

AL

IN, KY

TN

IN

TN, VA

TN

TX

LA, TX, OK

TX

NM, TX

TX

OK, TX

TX

NV, UT

UT

VT

NH, VT

VA

NC

VA, WV

VA

VA, WV

VA

TX

DC, MD

VA, WV

VA

WA

AK, MT, WA

WI

IL, WI

WI

cbatrody · Aug 28, 2015

Hi,

Try the following formula in C1:

=COUNTIF(A:A,"*"&B1&"*")

This is considering the state names are in column A and the values to lookup is in column B (B1)

dmshott · Aug 28, 2015

There are no values to look up in B1. I am trying to count the occurrences of the state names, but certain cells have more than one state and are divided by commas.

Sinon · Aug 28, 2015

cbatrody's formula accounts for cells where you have more than 1 state name. B1 is the cell where you have the state name you want to count. SO instead of having:
=COUNTIF(A:A,"*AZ*") the formula is =COUNTIF(A:A,"*"&B1&"*"), where B1 has the state name in it. I am assuming you want to know how many times AZ, AR etc are present in column A and cbatrody's formula does exactly that, state by state. You only need to put the state name you want in B1 and that way you can easily change it.

hiker95 · Aug 28, 2015

dmshott,

1. What version of Excel, and, Windows are you using?

2. Are you using a PC or a Mac?

Here is a macro solution for you to consider that uses two arrays in memory, and, the Scripting.Dictionary, and, will write the sorted results in columns C, and, D.

You can change the raw data worksheet name in the macro.

Sample raw data, and, results (not all rows are shown for brevity):

Excel 2007

A

B

C

D

1

ACO Service Area

Service Area

Count

2

AZ

AL

6

3

AZ

AR

10

4

AZ

10

5

AR

CA

32

6

AR

CO

4

7

AZ, NM

CT

14

8

AZ

DC

7

9

AZ

DE

5

10

AZ

FL

47

11

AZ

GA

18

12

AZ, TX

IA

9

13

CA

ID

2

14

CA

IL

27

15

CA

IN

22

16

CA

KS

9

17

CA

KY

14

18

CA

LA

7

19

CA

MA

20

CA, MO, TX

MD

21

Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code:

Sub GetUniqueCount()
' hiker95, 08/28/2015, ME878969
Dim a As Variant, d As Object, s, o As Variant
Dim lr As Long, i As Long, j As Long
With Sheets("Sheet1")   '<-- you can change the sheet name here
  lr = .Cells(.Rows.Count, 1).End(xlUp).Row
  a = .Range("A2:A" & lr)
  .Columns("C:D").ClearContents
  With CreateObject("Scripting.Dictionary")
    .CompareMode = vbTextCompare
    For i = LBound(a, 1) To UBound(a, 1)
      If InStr(a(i, 1), ", ") Then
        s = Split(a(i, 1), ", ")
        For j = LBound(s) To UBound(s)
          If .Exists(s(j)) Then
            .Item(s(j)) = .Item(s(j)) + 1
          Else
            .Add s(j), 1
          End If
        Next j
      Else
        If Not .Exists(a(i, 1)) Then
          .Add a(i, 1), 1
        Else
          .Item(a(i, 1)) = .Item(a(i, 1)) + 1
        End If
      End If
    Next i
    o = Application.Transpose(Array(.Keys, .Items))
  End With
  .Range("C1").Resize(, 2).Value = Array("Service Area", "Count")
  .Range("C2").Resize(UBound(o, 1), UBound(o, 2)) = o
  .Range("C2:D" & UBound(o, 1)).Sort key1:=.Range("C2"), order1:=1
  .Columns("C:D").AutoFit
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the GetUniqueCount macro.

hiker95 · Aug 28, 2015

dmshott,

Here is an updated macro solution for you to consider that uses two arrays in memory, and, the Scripting.Dictionary, and, will write the sorted results in columns C, and, D.

You can change the raw data worksheet name in the macro.

Sample raw data, and, results (not all rows are shown for brevity):

Excel 2007

A

B

C

D

1

ACO Service Area

Service Area

Count

2

AZ

AK

1

3

AZ

AL

6

4

AZ

AR

10

5

AR

AZ

10

6

AR

CA

32

7

AZ, NM

CO

4

8

AZ

CT

14

9

AZ

DC

7

10

AZ

DE

5

11

AZ

FL

47

12

AZ, TX

GA

18

13

CA

IA

9

14

CA

ID

2

15

CA

IL

27

16

CA

IN

22

17

CA

KS

9

18

CA

KY

14

19

CA

LA

7

20

CA, MO, TX

MA

20

Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Code:

Sub GetUniqueCount_V2()
' hiker95, 08/28/2015, ME878969
Dim a As Variant, d As Object, s, o As Variant
Dim lr As Long, i As Long, j As Long
With Sheets("Sheet1")   '<-- you can change the sheet name here
  lr = .Cells(.Rows.Count, 1).End(xlUp).Row
  a = .Range("A2:A" & lr)
  .Columns("C:D").ClearContents
  With CreateObject("Scripting.Dictionary")
    .CompareMode = vbTextCompare
    For i = LBound(a, 1) To UBound(a, 1)
      If InStr(a(i, 1), ", ") Then
        s = Split(a(i, 1), ", ")
        For j = LBound(s) To UBound(s)
          If .Exists(s(j)) Then
            .Item(s(j)) = .Item(s(j)) + 1
          Else
            .Add s(j), 1
          End If
        Next j
      Else
        If Not .Exists(a(i, 1)) Then
          .Add a(i, 1), 1
        Else
          .Item(a(i, 1)) = .Item(a(i, 1)) + 1
        End If
      End If
    Next i
    o = Application.Transpose(Array(.Keys, .Items))
  End With
  .Range("C1").Resize(, 2).Value = Array("Service Area", "Count")
  .Range("C2").Resize(UBound(o, 1), UBound(o, 2)) = o
  .Range("C2:D" & UBound(o, 1) + 1).Sort key1:=.Range("C2"), order1:=1
  .Columns("C:D").AutoFit
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the GetUniqueCount_V2 macro.

hiker95 · Aug 28, 2015

dmshott,

Here is a formula solution:

Excel 2007

A

B

C

D

1

ACO Service Area

Service Area

Count

2

AZ

AK

1

3

AZ

AL

6

4

AZ

AR

11

5

AR

AZ

10

6

AR

CA

32

7

AZ, NM

CO

5

8

AZ

CT

14

9

AZ

DC

7

10

AZ

DE

5

11

AZ

FL

47

12

AZ, TX

GA

18

13

CA

IA

9

14

CA

ID

2

15

CA

IL

27

16

CA

IN

22

17

CA

KS

9

18

CA

KY

14

19

CA

LA

7

20

CA, MO, TX

MA

20

21

CA

MD

21

22

CA

ME

4

23

CA, MS, OH

MI

21

24

CA

MN

4

25

CA

MO

15

26

CA

MS

9

27

CA

MT

3

28

CA, FL

NC

19

29

CA

ND

2

30

CA

NE

3

31

CA

NH

9

32

CA, DC, FL, GA, LA, NC, SC, TN, TX

NJ

26

33

CA

NM

3

34

CA, PA, TN

NV

5

35

CA, IN, MI

NY

35

36

CA, IL, IN, IA

OH

18

37

CA

OK

7

38

OR, WA

OR

4

39

CA

PA

25

40

CA

PR

2

41

IN, OH

RI

4

42

IN

SC

9

43

CA

SD

1

44

CA

TN

15

45

CO

TX

35

46

CO

UT

3

47

CO

VA

18

48

CT, NY

VT

4

49

CT

WA

4

50

CT

WI

10

51

CT, NY

WV

7

52

CT

WY

2

53

CT

Sheet1

Cell Formulas
Range	Formula
D2	=COUNTIF(A:A,""&C2&"")

With the state column sorted, range C2:C52, the formula in cell D2, copied down:

=COUNTIF(A:A,"*"&C2&"*")

URGENT: Help with counting how often a value occurs

dmshott

New Member

cbatrody

Board Regular

dmshott

New Member

Sinon

Active Member

hiker95

Well-known Member

hiker95

Well-known Member

hiker95

Well-known Member

Similar threads

Share this page

URGENT: Help with counting how often a value occurs

dmshott

New Member

cbatrody

Board Regular

dmshott

New Member

Sinon

Active Member

hiker95

Well-known Member

hiker95

Well-known Member

hiker95

Well-known Member

Similar threads

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock