How do I count how many times this category appeared only if the value in the column name is not equals to "N/A"

Question

I have these sample data:https://docs.google.com/spreadsheets/d/1TSpuMRktIRAoACH5s1Dk9189NsNpJwjRtNaR5QROZg8/edit?usp=sharing

I wanted to count how many times a certain category has appeared only if the data under the column name is not equal to "N/A"

What I currently has done is that it still counts as "1" even if the value under the name is "N/A"

Sample Ouput to what I have done so far

Answer 1

I created a Table from your data so as to have dynamic references, but you could use normal addressing without a table if you prefer

In column G, create a distinct list of the categories. In Row 1, create a distinct list of the Regions. In Excel 2016, you could do this using the Advanced Filter as well as other methods.

Then, in an adjacent cell, enter the formula:

=COUNTIFS(Categories[[Category]:[Category]],$G2,Categories[[Name]:[Name]],"<>N/A",Categories[[Regions]:[Regions]],H$1)

and fill down and across.

If you don't need this to be dynamic, it can also be done easily using Power Query, available in Windows Excel 2010+ and Office 365 Excel

• Select some cell in your original table
• Data => Get&Transform => From Table/Range or From within sheet
• When the PQ UI opens, navigate to Home => Advanced Editor
• Make note of the Table Name in Line 2 of the code.
• Replace the existing code with the M-Code below
• Change the table name in line 2 of the pasted code to your "real" table name
• Examine any comments, and also the Applied Steps window, to better understand the algorithm and steps

M Code

let

//Change table name in next line to your actual table name
    Source = Excel.CurrentWorkbook(){[Name="Categories"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Regions", type text}, {"Category", type text}, {"Name", type text}}),

//Group by Regions and Category
//Then Count if `Name` <> "N/A"
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Regions","Category"}, {
        {"Count", each List.Count(List.Select([Name], each _ <> "N/A"))}}),

//Pivot on the Regions to get a separate column for each Region
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[Regions]), "Regions", "Count"),

//Set the data types
    typeIt = Table.TransformColumnTypes(#"Pivoted Column", 
        List.Transform(List.RemoveFirstN(Table.ColumnNames(#"Pivoted Column")), each {_, Int64.Type}))
in
    typeIt