Need help with a formula
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!
Answers

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])

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.

If I am understanding your sheet layout correctly the below should work.
COUNTIFS(A:A,>3,Month:Month,=7)/(COUNTIF(Month:Month,=7)

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({20232024 Call Monitoring Archive Range 2}">3",{{20232024 Call Monitoring Archive Month}=7)/COUNTIFS({20232024 Call Monitoring Archive Range 5}<>"",{{20232024 Call Monitoring Archive Month}=7)

Hollie, try the below
=COUNTIFS({20232024 Call Monitoring Archive Range 2},>3,{20232024 Call Monitoring Archive Month}=7)/COUNTIFS({20232024 Call Monitoring Archive Range 5}<>"",{20232024 Call Monitoring Archive Month}=7)

Thank you jpaul  but that returns #INVALID OPERATION.

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

There are a few syntax issues that need to be cleaned up first. Try this:
=COUNTIFS({20232024 Call Monitoring Archive Range 2},@cell>3,{20232024 Call Monitoring Archive Month}, @cell=7)/COUNTIFS({20232024 Call Monitoring Archive Range 5}, @cell<>"",{20232024 Call Monitoring Archive Month}, @cell=7)

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!

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.

Thanks! The ranges are all entire columns.

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

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.

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.
Help Article Resources
Categories
Check out the Formula Handbook template!