Need help with a formula

Options

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:

Answers

  • jpaul
    jpaul ✭✭✭✭
    edited 06/24/23
    Options

    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
    Options

    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
    Hollie Green ✭✭✭✭✭✭
    edited 06/26/23
    Options

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

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

  • Ldonoghue
    Options

    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
    jpaul ✭✭✭✭
    Options

    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
    Options

    Thank you jpaul - but that returns #INVALID OPERATION.

  • jpaul
    jpaul ✭✭✭✭
    Options

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    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
    Options

    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
    Paul Newcome ✭✭✭✭✭✭
    Options

    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
    Options

    Thanks! The ranges are all entire columns.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    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
    Options

    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
    Ldonoghue ✭✭
    edited 07/18/23
    Options

    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!