cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Super User I
Super User I

Count instances of string in multiple columns

Hi,

 

Been searching the forums but can't find anything that exactly replicates what I'm trying to do - I currently have an Excel file that has the following column names (along with various others which I will want to slice on later, but doesn't affect this calculation) and some basic sample data:

 

Hire Request Date - Hirer 1 - Hirer 2 - Hirer 3 - Hirer 4

1/1/2017 - Bob - (blank) - (blank) - (blank)

1/1/2017 - Alice - Bob - (blank) - (blank)

2/1/2017 - (blank) - Bob - Clive - (blank)

3/1/2017 - Clive - (blank) - Bob - (blank)

 

The four hirer columns are populated either by a hirer name (of which there's around 20 possibilities which apply to every column, I have a separate table listing them all related to hirer 1 as active and the other hirer columns as inactive), or are blank, and more than one column can be non-blank at the same time in any given row. Very simply, I want to be able to count the total number of times each hirer appears in all the columns, so the expected results here would be Bob 4, Alice 1, Clive 2. In Excel this is just four countifs, but while I can get one column to work fine using counta (although it returns blanks as an option, which I don't want), if I try to add two or more of the columns together, it breaks down and provides wildly incorrect results. Any suggestions on how I can make progress on this?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Champion
Community Champion

Re: Count instances of string in multiple columns

@jthomson

 

Hi try with this:

 

CountinAllHirerColumns =
CALCULATE (
    COUNTROWS ( Table1 ),
    USERELATIONSHIP ( HirerTable[Hirer], Table1[Hirer 1] )
)
    + CALCULATE (
        COUNTROWS ( Table1 ),
        USERELATIONSHIP ( HirerTable[Hirer], Table1[Hirer 2] )
    )
    + CALCULATE (
        COUNTROWS ( Table1 ),
        USERELATIONSHIP ( HirerTable[Hirer], Table1[Hirer 3] )
    )

After that in your visual filter:

 

img.png

 

Regards

 

Victor

Lima - Peru




Lima - Peru

View solution in original post

5 REPLIES 5
Highlighted
Community Champion
Community Champion

Re: Count instances of string in multiple columns

@jthomson

 

Hi try with this:

 

CountinAllHirerColumns =
CALCULATE (
    COUNTROWS ( Table1 ),
    USERELATIONSHIP ( HirerTable[Hirer], Table1[Hirer 1] )
)
    + CALCULATE (
        COUNTROWS ( Table1 ),
        USERELATIONSHIP ( HirerTable[Hirer], Table1[Hirer 2] )
    )
    + CALCULATE (
        COUNTROWS ( Table1 ),
        USERELATIONSHIP ( HirerTable[Hirer], Table1[Hirer 3] )
    )

After that in your visual filter:

 

img.png

 

Regards

 

Victor

Lima - Peru




Lima - Peru

View solution in original post

Highlighted
Microsoft
Microsoft

Re: Count instances of string in multiple columns

Hi @jthomson,

After research and test, the @Vvelarde solution is solid. Please folllow and mark the right reply as answer. And I am glad to help you if you still have any problems.

Best Regards,
Angelia

Highlighted
Regular Visitor

Re: Count instances of string in multiple columns

Would you happen to have an idea what to do if there were 700+ Columns? In my case, I am looking for specific string in all these columns.
I can get the Results in excel by doing COUNTIF(A1:A700,"PASS") and it gives me the total count.
Please let me know if there is a solution to this. @Vvelarde 

 

Thanks in Advance

Highlighted
Super User V
Super User V

Re: Count instances of string in multiple columns

Hi,

In your text, you state 700+ columns but in your formula you have 700 rows.  Please clarify.  If there are 700 columns, you may have to Unpivot columns and then it becomes an easy one to solve.

Share some data and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
Frequent Visitor

Re: Count instances of string in multiple columns

@Vvelarde 

Hi, adding to that, can this be used for dynamic number of Hirer columns in Table1?

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors