Need help with a formula

Ldonoghue · 06/24/23

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 · 06/24/23

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 · 06/26/23

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

Ldonoghue · 06/26/23

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 · 06/26/23

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 · 06/26/23

Thank you jpaul - but that returns #INVALID OPERATION.

jpaul · 06/26/23

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

Paul Newcome · 06/26/23

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 · 06/27/23

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 · 06/27/23

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 · 06/27/23

Thanks! The ranges are all entire columns.

Paul Newcome · 06/27/23

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 · 06/29/23

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 · 07/18/23

Thank you for your responses and suggestions@Curtis Langeand@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.

Need help with a formula

Answers

Help Article Resources

Categories