Unclear which way to go about data analysis

[liz123395]

Hi I have a list of companies where there are up to 3 founders in each with their own demographics all in the same row. I am looking for a way to analyze the demographics of each company by person. So for instance I want to know if the company has a female founder or a female black founder etc. There are 1000s of companies so Im looking for a way to do this at scale. Do you have any ideas? I was thinking I could do If statements but due to the endless possibilities I need to analyze this could take a while? (Because I’d have to create an if statement for each possibility - example, if f4=female and g4=black or if h4=female and , if i4=black, etc) 

[#2]

You could create a pivot table where you drag the characteristics you are interested in analyzing to the Row area of the pivot. Then drag any textual field to the Sum area (the bottom-right box, it will be counted). Then insert a slicer for each row field to ease filtering.

[#3]

@liz123395 

It all depends upon the consistency of your data and the version of Excel that you use.  For example, you may need to filter the demographics rows from the table to separate it from financial data.  If name, gender and ethnicity data form a regular pattern, you could use WRAPROWS to return a pivot table normalised array.  If you were using 365 beta, you could pivot this to build a table that counts individuals by gender and ethnicity.  There are rather a lot if 'ifs' here!

= LET(
    demographics, FILTER(companyData, dataType="demographics"),
    array,        WRAPROWS(TOCOL(demographics), 3),
    name,         CHOOSECOLS(array, 1),
    gender,       CHOOSECOLS(array, 2),
    ethnicity,    CHOOSECOLS(array, 3),
    PIVOTBY(gender, ethnicity, name, COUNTA)
  )

The sort of result this might generate would be of the form