CODEWITHSUNDEEP
excel
Grega Kovac
Grega Kovac

Reputation: 1

Count of values based on several criteria

First of all I want to thank you all for even reading this and helping me out. I have a problem that I can't solve and reading Excel tips and tricks did not help.

I have a table (below only with much more rows) where I have to get a unique count of how many times a person worked from home and how many times the/she went to the office. The result I'm looking for is:

required result

I have exhausted my wisdom and hope for your help.

part1 of the table part2 of the table

TABLE:

PERSON NUMBER   PERSON NAME         DATE            REPORTED HOU    LOCATION
7272            John Doe            2021-03-01      3               WFH
7272            John Doe            2021-03-01      1               WFH
7272            John Doe            2021-03-01      2               WFH
7272            John Doe            2021-03-01      2               WFH
7272            John Doe            2021-03-02      2               WFH
7272            John Doe            2021-03-02      2               WFH
7272            John Doe            2021-03-02      2               WFH
7272            John Doe            2021-03-03      2               WFH
7272            John Doe            2021-03-03      1               WFH
7272            John Doe            2021-03-03      1               WFH
7272            John Doe            2021-03-03      2               WFH
7272            John Doe            2021-03-03      2               WFH
7272            John Doe            2021-03-04      2               WFH
7272            John Doe            2021-03-04      1               WFH
7272            John Doe            2021-03-04      3               WFH
7272            John Doe            2021-03-04      1               WFH
7272            John Doe            2021-03-04      1               WFH
7272            John Doe            2021-03-05      2               WFH
7272            John Doe            2021-03-05      1               WFH
7272            John Doe            2021-03-05      1               WFH
7272            John Doe            2021-03-05      2               WFH
7272            John Doe            2021-03-05      2               WFH
7272            John Doe            2021-03-08      3               WFH
7272            John Doe            2021-03-08      2               WFH
7272            John Doe            2021-03-08      1               WFH
7272            John Doe            2021-03-08      2               WFH
7272            John Doe            2021-03-09      1               WFH
7272            John Doe            2021-03-09      1               WFH
7272            John Doe            2021-03-09      1               WFH
7272            John Doe            2021-03-09      3               WFH
7272            John Doe            2021-03-09      1               WFH
7272            John Doe            2021-03-09      1               WFH
7272            John Doe            2021-03-10      1               WFH
7272            John Doe            2021-03-10      1               WFH
7272            John Doe            2021-03-10      1               WFH
7272            John Doe            2021-03-10      5               WFH
7272            John Doe            2021-03-02      2               WFH
7273            Jane Doe            2021-03-01      3               WFH
7273            Jane Doe            2021-03-01      1               WFH
7273            Jane Doe            2021-03-01      2               WFH
7273            Jane Doe            2021-03-01      2               WFH
7273            Jane Doe            2021-03-02      2               Office
7273            Jane Doe            2021-03-02      2               Office
7273            Jane Doe            2021-03-02      2               Office
7273            Jane Doe            2021-03-03      2               WFH
7273            Jane Doe            2021-03-03      1               WFH
7273            Jane Doe            2021-03-03      1               WFH
7273            Jane Doe            2021-03-03      2               WFH
7273            Jane Doe            2021-03-03      2               WFH
7273            Jane Doe            2021-03-04      2               Office
7273            Jane Doe            2021-03-04      1               Office
7273            Jane Doe            2021-03-04      3               Office
7273            Jane Doe            2021-03-04      1               Office
7273            Jane Doe            2021-03-04      1               Office
7273            Jane Doe            2021-03-05      2               WFH
7273            Jane Doe            2021-03-05      1               WFH
7273            Jane Doe            2021-03-05      1               WFH
7273            Jane Doe            2021-03-05      2               WFH
7273            Jane Doe            2021-03-05      2               WFH
7273            Jane Doe            2021-03-08      3               Office
7273            Jane Doe            2021-03-08      2               Office
7273            Jane Doe            2021-03-08      1               Office
7273            Jane Doe            2021-03-08      2               Office
7273            Jane Doe            2021-03-09      1               WFH
7273            Jane Doe            2021-03-09      1               WFH
7273            Jane Doe            2021-03-09      1               WFH
7273            Jane Doe            2021-03-09      3               WFH
7273            Jane Doe            2021-03-09      1               WFH
7273            Jane Doe            2021-03-09      1               WFH
7273            Jane Doe            2021-03-10      1               Office
7273            Jane Doe            2021-03-10      1               Office
7273            Jane Doe            2021-03-10      1               Office
7273            Jane Doe            2021-03-10      5               Office
7273            Jane Doe            2021-03-10      2               Office

Upvotes: 0

Views: 63

Answers (3)

Zev Spitz
Zev Spitz

Reputation: 15377

If you are interested in a VBA solution --

Add a reference (Tools -> References... from the VBA editor) to Microsoft ActiveX Data Objects (the latest version; usually 6.1).

Then, you can write code like the following:

Const filepath As String = "C:\path\to\excel\file.xlsx"
Const sheetname As String = "Sheet1"

Dim connectionString As String
connectionString = _
    "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=""" & filepath & """;" & _
    "Extended Properties=""Excel 12.0;HDR=Yes"""

    ' If your data is in a macro-enabled file (.xlsm), the previous line should
    ' look like this:
    ' "Extended Properties=""Excel 12.0 Macro;HDR=No"""

Dim sql As String
sql = _
    "SELECT [PERSON NAME], COUNT(*) " & _
    "FROM [" & sheetname & "$] " & _
    "GROUP BY [PERSON NAME]"

' If your data is only part of the worksheet, you can specify the range as follows:
' sql = _
'    "SELECT [PERSON NAME], COUNT(*) " & _
'    "FROM [" & sheetname & "A1:E10000$] " & _
'    "GROUP BY [PERSON NAME]"

Dim rs As New ADODB.Recordset
rs.Open sql, connectionString

Worksheets.Add.Range("A1").CopyFromRecordset(rs)

This code will add a new worksheet to the active workbook with the grouped data in it.

References:

Microsoft Access SQL (used when querying Excel files):

ADODB:

Excel:

Upvotes: 0

Solar Mike
Solar Mike

Reputation: 8415

This goes with the answer from Foxfire and Burns and Burns:

The unique list of names can be automatically produced using unique() as shown:

enter image description here

However, it can be built manually using a copy of the names and the "Remove Duplicates" button.

Upvotes: 0

Foxfire And Burns And Burns
Foxfire And Burns And Burns

Reputation: 11998

You can count unique records with SUMPRODUCT and COUNTIF with multiple criteria:

https://www.ablebits.com/office-addins-blog/2016/04/07/how-to-count-distinct-and-unique-values-in-excel/

I got this:

enter image description here

My formula in H5 is:

=SUMPRODUCT(IFERROR(1/COUNTIFS($B$2:$B$75;$B$2:$B$75;$C$2:$C$75;$C$2:$C$75;$E$2:$E$75;$E$2:$E$75;$B$2:$B$75;$G5;$E$2:$E$75;H$4);0))

As you can see, it returns the exptected output (notice my headers are WFH and Office)

Upvotes: 1

Related Questions