I have a doozy, i need formulas for each step & variable to summarize then create a dashboard. help!

Bobby1

Step 1---How Many (Limited Duration are full-time)

(Limited Duration are part-time)

(Contractors are full-time)

(Contractors are part-time)

etc.....

step 2-----Then If you are a contractor, ( How many Full-time /contractors work for Deloitte)...etc...

( How many Part-time /contractors work for Deloitte)...etc...

step 3------How many ( How many Full-time /contractors work for Deloitte/ are in the same meetings on specific days-Mon-Tues etc....)

step 4 --- How many Full-time /contractors work for Deloitte/ are in the same meetings the week )etc.....

Gia Thinh

Hi Bobby1,

You should create a new Metric sheet that calculate metrics in above steps from your source sheet. Then from this Metric sheet you can show these metrics in your dashboard.

COUNTIFS will be used for calculation. Look at the below formulas for your reference.

Hope that help.

Bobby1

@Gia Thinh thank you

in the summary sheet it returned "UNPARSEABLE

Gia Thinh

The sheet summary is in your source sheet?

can you post screen shots of the source sheet?

Andrée Starå

Hi @Bobby1

I hope you're well and safe!

Try something like this. (I suspect another region + there was a semi:colon, comma missing)

I've added both regional options for one of the formulas below.

=
COUNTIF([Staff Status]:[Staff Status], "Limited Duration",
[Schedule Status]: [Schedule Status],"Full-time")

=
COUNTIF([Staff Status]:[Staff Status]; "Limited Duration";
[Schedule Status]: [Schedule Status];"Full-time")

Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma.

Did that work/help?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Kelly Moore

Hey Bobby1

Your screenshot is showing that you have used a COUNTIF (singular) function.

A COUNTIF (singular) only allows one criteria in the formula. A COUNTIFS (plural) works in any situation as it allows any number of criteria - from one to whatever. This is why that function was recommended by Gia above. Change your function to COUNTIFS. As Andrée noted, you may have the regional comma-semicolon differences.

Kelly

Andrée Starå

@Kelly Moore

Ouch! I missed the Countifs part. Time for coffee!

@Bobby1

Here are the modified formulas with COUNTIFS instead.

EDIT: Added more options.

=
COUNTIFS([Staff Status]:[Staff Status], "Limited Duration",
[Schedule Status]: [Schedule Status],"Full-time")

=
COUNTIFS([Staff Status]:[Staff Status]; "Limited Duration";
[Schedule Status]: [Schedule Status];"Full-time")

=
COUNTIFS([Staff Status]:[Staff Status], "Limited Duration",
[Schedule Status]: [Schedule Status],"Part-time")

=
COUNTIFS([Staff Status]:[Staff Status]; "Limited Duration";
[Schedule Status]: [Schedule Status];"Part-time")

=
COUNTIFS([Staff Status]:[Staff Status], "Contractor",
[Schedule Status]: [Schedule Status],"Full-time")

=
COUNTIFS([Staff Status]:[Staff Status]; "Contractor";
[Schedule Status]: [Schedule Status];"Full-time")

=
COUNTIFS([Staff Status]:[Staff Status], "Contractor",
[Schedule Status]: [Schedule Status],"Part-time")

=
COUNTIFS([Staff Status]:[Staff Status]; "Contractor";
[Schedule Status]: [Schedule Status];"Part-time")