counting text across multiple columns with a filter

Hi,

Hope you can help me! I have a spreadsheet with multiple identical columns that contain lists of interventions that employees do with clients (I.e., Intervention 1, Intervention 2, Intervention 3, etc). Each Intervention column contains the same list of 20 interventions. I can use the COUNTIF function to determine the number of specific interventions across these columns; however, I want to filter this by employee. Is there a way to collapse across these columns in a pivot table? Any advice would be greatly appreciated.

C

It sounds like a pivot table or a SUMPRODUCT would work for you but I am unclear as to how your data is set up. Can you upload an example spreadsheet with a few columns and rows?

ChemistB

I have attached a sample ... basically want to know how I can determine the number of times each staff member engages in each intervention. Thanks for your help.

You could add an extra column and use counta(<the data column range>).
This will count the non-blank cells. Then use a pivot table to do a report summed by user.

example attached (also sample3 showing how to do it by 'intervention type' in case it is useful .. they will both need to be in C:\ to run I think)

Here's a solution using SumProduct. I created a table starting in cell N1. However you said there were 20 interventions and I have 29 or so, so maybe I'm charting the wrong thing. The formula in each cell is similar to this

Please Login or Register  to view this content.

where 0$1 is where the Staff team is and $N2 is where the intervention type is.

ChemistB