Formula help; calculating a percentage of a value by month

Hello,

I want to calculate a percentage for each month starting with current month and going back 6 months. I want the formula to recognize when the answer is "pre" in a column where the possible answers are "pre", "post" or "N/A" and calculate the percentage of the answers that are "pre" in that month. I tried the following formula but it did not work.

=IFERROR(
COUNTIFS(Date:Date, MONTH(@cell) = MONTH(TODAY()) - X, Date:Date, YEAR(@cell) = YEAR(TODAY()) - IF(MONTH(TODAY()) <= X, 1, 0), Answer:Answer, "pre") /
COUNTIFS(Date:Date, MONTH(@cell) = MONTH(TODAY()) - X, Date:Date, YEAR(@cell) = YEAR(TODAY()) - IF(MONTH(TODAY()) <= X, 1, 0)),
0
)

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

Give this a try:

=COUNTIFS(Answer:Answer, @cell = "pre", Date:Date, AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()) + IF(MONTH(TODAY()) > 6, -6, 6), IFERROR(YEAR(@cell), 0) = YEAR(TODAY()) - IF(MONTH(TODAY()) <= 6, 1, 0))) / COUNTIFS(Date:Date, AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()) + IF(MONTH(TODAY()) > 6, -6, 6), IFERROR(YEAR(@cell), 0) = YEAR(TODAY()) - IF(MONTH(TODAY()) <= 6, 1, 0)))

CaitP

Thank you. This gave me an output, but it doesn't quite make sense with the data.

Paul Newcome

CAn you provide an example?

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.