Returning a Percentage Based on Particular Criteria

I am trying to work out a percentage for some KPIs.

I have a sheet I am recording various KPI entries on (see below)

I have a metrics sheet set up so, I can break the KPI results down by month by referencing this sheet.

Taking KPI 1 (in the example shown above), I need a formula that will return a percentage of the number of KPI 1 entries where the KPI 1 helper column shows a 0 for the month of April. I cam currently using the following formula but it's returning a result of 0% when I would expect it to return a result of 50% looking at the total number of KPI 1 entries for the month of April.

=COUNTIFS({03. KPI Register l KPI Category}, 1, {03. KPI Register l Date Recorded}, IFERROR(MONTH(@cell ), 0) = 4) / COUNTIFS({03. KPI Register l KPI1 Helper}, 0, {03. KPI Register l Signed Off Date}, IFERROR(MONTH(@cell ), 0) = 4)

Whats wrong?!

TIA

Find more posts tagged with

Formulas

Accepted answers

DKazatsky2

Hi @Cheryl C,

The way the formula is written is evaluating to "1 / 0" which is why you are getting "0" as a result.

I do see a few things that are causing issues.

This section, "{03. KPI Register l KPI Category}, 1" is resulting in "0" since that field is actually a text string, not a numeric.
The second part of the formula should be using the value of all entries with "KPI 1" and date, regardless of category to get the percentage
Which date column do you want to check against, you reference each if different parts of the equation

This formula will get produce the correct results but you may need to change the date reference if I chose the wrong one.

=COUNTIFS({03. KPI Register l KPI Category}, "KPI 1", {03. KPI Register l KPI1 Helper}, 0, {03. KPI Register l Signed Off Date}, IFERROR(MONTH(@cell ), 0) = 4) / COUNTIFS({03. KPI Register l KPI Category}, "KPI 1", {03. KPI Register l Signed Off Date}, IFERROR(MONTH(@cell ), 0) = 4)

Hope this helps,

Dabe

All comments

DKazatsky2

Hi @Cheryl C,

The way the formula is written is evaluating to "1 / 0" which is why you are getting "0" as a result.

I do see a few things that are causing issues.

This section, "{03. KPI Register l KPI Category}, 1" is resulting in "0" since that field is actually a text string, not a numeric.
The second part of the formula should be using the value of all entries with "KPI 1" and date, regardless of category to get the percentage
Which date column do you want to check against, you reference each if different parts of the equation

This formula will get produce the correct results but you may need to change the date reference if I chose the wrong one.

Hope this helps,

Dabe

Cheryl C

Perfect. Thanks Dabe!

DKazatsky2

Ha, I misspelled my own name, it's Dave. Doh!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.