# 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!

Tags:

• ✭✭✭✭
edited 06/24/23

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.

• ✭✭✭✭✭✭
edited 06/26/23

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({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)

• ✭✭✭✭

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)

• 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({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)

• 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)?

• Employee

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.

• edited 07/18/23

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!