Need help with a formula

Ldonoghue

I am trying to calculate a percentage of items that meet a specified criteria during a particular period. I can calculate the percentage correctly [=COUNTIF(A:A>3)/COUNT(B:B)] but can't figure out how to add the filter for the period (it's a "Month" column).

=IF(Month:Month=7(COUNTIF(A:A,">3")/(COUNT(B:B),"") does not work - it returns either an UNPARSEABLE or INVALID OPERATION error. I get an invalid operation errror with just the IF statement (IF Month:Month =7, "Yep", "Nope"). So I'm assuming IF is not the formula to use - but I do not know how to find the correct option. Thanks in advance for suggestions!

jpaul

are your column headings actually "A" and "B"?

Your IF statement has a typo in it so that might be why it's not running properly. Try Pasting the below in and see if that works.

=IF(Month:Month =7, "Yep", "Nope")

Try using the below formula to fix your bigger question.

=(COUNTIF([A]:[A], 7)) / COUNT([B]:[B])

Ldonoghue

Hi and thanks for your response! The typo was just in the example; the syntax is correct in the actual sheet. I think the issue is that "IF" is not the right formula to use. The countif/count part works - it's adding the IF statement that doesn't.

Hollie Green

If I am understanding your sheet layout correctly the below should work.

COUNTIFS(A:A,>3,Month:Month,=7)/(COUNTIF(Month:Month,=7)

Ldonoghue

Thank you for responding Hollie! That did not work but it leaves out part of the equation.

I want to calculate the percentage of records that achieved a certain score in a given period. So I'm saying for this period (e.g. "June"), count all the records scoring ">3" (COUNTIF) and divide that number by the total number of records evaluated in that period (COUNT). I tried modifying your suggestion to include a COUNTIFS at the end of the formula also, but either I did it incorrectly or it's still not the right formula. Here's how it looks...

=COUNTIFS({2023-2024 Call Monitoring Archive Range 2}">3",{{2023-2024 Call Monitoring Archive Month}=7)/COUNTIFS({2023-2024 Call Monitoring Archive Range 5}<>"",{{2023-2024 Call Monitoring Archive Month}=7)

jpaul

Hollie, try the below

=COUNTIFS({2023-2024 Call Monitoring Archive Range 2},>3,{2023-2024 Call Monitoring Archive Month}=7)/COUNTIFS({2023-2024 Call Monitoring Archive Range 5}<>"",{2023-2024 Call Monitoring Archive Month}=7)

Ldonoghue

Thank you jpaul - but that returns #INVALID OPERATION.

jpaul

Are you cross referencing a different sheet or are all of these on the same sheet?

Paul Newcome

There are a few syntax issues that need to be cleaned up first. Try this:

=COUNTIFS({2023-2024 Call Monitoring Archive Range 2},@cell>3,{2023-2024 Call Monitoring Archive Month}, @cell=7)/COUNTIFS({2023-2024 Call Monitoring Archive Range 5}, @cell<>"",{2023-2024 Call Monitoring Archive Month}, @cell=7)

Ldonoghue

Returns #Incorrect Argument :( Thank you for the syntax corrections nonetheless - it is helpful for me when I'm trying to find missing or misplaced commas, etc!

Paul Newcome

Double check your ranges. They should all be the same size/shape. In this instance, most likely entire columns by clicking on the appropriate column header when setting up.

Ldonoghue

Thanks! The ranges are all entire columns.

Paul Newcome

Are you able to provide a screenshot of the formula actually open in the sheet (as if you were going to edit it)?

Curtis Lange

Hello Ldonoghue.

I think I understand what you're trying to do. I broke the formula into two parts.

First we need to get the number of entries that have a score greater than 3 for the month 7.

=COUNTIFS(Month:Month, =7, Score:Score, >3)

Once we have that value we then need to divide that by the number of entries we have for that month.

=INDEX(Total:Total, MATCH(7, Month:Month, 0))

I then combined the formulas together. I added extra paranthesis to ensure order of operations.

=(COUNTIFS(Month:Month, =7, Score:Score, >3)) / (INDEX(Total:Total, MATCH(7, Month:Month, 0)))

Let me know if this works for you.

Ldonoghue

Thank you for your responses and suggestions @Curtis Lange and @Paul Newcome! I continue to run into issues - I think in the case of the INDEX formula, possibly because of the type of data in the column or because it's referencing a different sheet. I think I've found a workaround that will get me what I need, but you helped get me on the path! Sorry I didn't post this sooner.