Countif - Multiple outcomes?

Jason P · 07/14/20

Hi,

We run a design Dept sheet accessed by 5 sales reps who can select 1 Request Type from 10 set options that don't change (RT column) IE: Drawing OR Drawing and costs OR TSR OR Permit etc.. These I count easy enough rep x rep (countifs) with that data displayed on a dashboard. When a RT is assessed by the design team it is placed in to a Status (column drop down) which has 7 types that can change based on the progress of the task, IE: In progress - Not started - Revision - Pending etc.. again I count these per above.

I have been asked to show in a stacked bar chart on the dashboard counts against each RT and their status, issue is this can be different for the 1 RT type. IE: Drawing (RT) can have any 1 of the 7 status types, first day Drawing could be at In Progress, next day at revision.

Any suggestions on how to count.

Cheers Jason.

RossL · 07/14/20

Jason P · 07/14/20

@RossL above mine too.

SoS | Dan Palenchar · 07/15/20

You should build a separate metrics sheet that organizes this data and build charts off of that. You can have your RTs going down the primary column and each status option going across (or vice versa). Use cross sheet COUNTIF formulas to get the data into what is basically now a data table. Use this data to create your bar chart.

Jason P · 07/15/20

@SoS | Dan Palenchar Cheers Dan, will have a look at that - I did start that but then thought there might be a formula to manage that, hard to imagine I'm the only one out there with this issue.