Formula help; calculating a percentage of a value by month

CaitP

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
)

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?