Count across 5 columns for same values

[Anonymous]

Hi

my excel data has 5 columns that contain 3 common values. (Surgery/Seles/Leding team) or are empty

My Graph set up:

• Axis - Hospital spec 1
• Value - count of name

-say (Surgery/Seles/Leding team) is respectively D, I, L

Now how can I add remaining 4 columns to the graph to get total across all 5 surgeries ? It does not work by default

Beside there are nulls that i want to filter out. That is easy for 1 column but not sure how it would be when there is 5.

Excluding rows from one column that are null also deleate filled ones from the other . Look at the excel data above.

I tried play with query editor:

- pivot  reduced number of categories (Surgery/Seles/Leding team) to just 1 - not sure why

- Transpose - gives me thousand name columns

I was thinking of getting total count for each column per category. (would need not count nulls) but how I get it back to graph above?

Any idea ?

[Vvelarde · Accepted solution]

@Anonymous 

Hi, In Query Editor try this:

-Select the columns with Hospital Spec (1 to 5)

-Transform - Unpivot Columns

-Filter No Blanks Columns in This New Column

Regards

Victor

[Anonymous]

Magic 🙂 Works like a charm 

Thank you so much