Countifs not calculating as expected

Whenever I change a field to NA it should exclude counting it into the average but it is not doing that. It is staying at 50% but should be 100%. Please let me know what I need to do. See screen shot.

=(SUM(Yes4, Yes6)) / ((COUNT([Department - ie: Sales Eng, TDE's etc]4, [Department - ie: Sales Eng, TDE's etc]6)) - (COUNTIFS(Yes4, "NA", Yes6, "NA")))

Thank you.

Find more posts tagged with

Comments

Brian W

COUNTIFS is looking for both criteria, not either. I think you need to use COUNT with OR:

=(SUM(Yes4, Yes6)) / ((COUNT([Department - ie: Sales Eng, TDE's etc]4, [Department - ie: Sales Eng, TDE's etc]6)) - (COUNT(OR(Yes4 = "NA", Yes6 = "NA"))))

GetITDone

Hi thanks for your suggestion! I just had to tweak it to look like this in order for it to calculate however

1. when I select both questions to answer Yes , I get 200% where I only want 100%. The N/A portion works though.

=(SUM(Yes4, Yes6)) / ((COUNT([Department - ie: Sales Eng, TDE's etc]4, [Department - ie: Sales Eng, TDE's etc]6) - (COUNT(OR(Yes4 = "NA", Yes6 = "NA"))))) (pic attached)

2. when I select 1 yes and 1 no I need to see 50% whereas the results 100% (pic attached)

I need it to score only on yes and no answers and when NA is selected it makes sure not to average into the equation.

any thought would be great. This has been very trying. :-(

Capture200%.PNG

Capture 50%.PNG

Brian W

Right. I'm not sure that COUNT/OR combination is even functioning correctly. Rather than doing the whole calculation, could you simply use AVG to average the cells with numbers:

=AVG(Yes4, Yes6)

That will average only numbers and ignore NA entries.

Brian W

If you do need to use your original formula, I think you would have to do it this way:

=(SUM(Yes4, Yes6)) / (COUNT([Department - ie: Sales Eng, TDE's etc]4, [Department - ie: Sales Eng, TDE's etc]6) - (COUNTIF(Yes4, ="NA") + COUNTIF(Yes6, ="NA")))

Paul Newcome

You could also try something along the lines of

=SUM(CHILDREN(Yes@row)) / COUNTIFS(CHILDREN(Yes@row), ISNUMBER(@cell))

This will sum the range up (which only looks at numbers anyway), then divide it by how many cells in the range contain a number (leaves out the N/A's).

Using the CHILDREN function also allows the addition or deletion of rows without having to worry about whether or not a range within a formula needs adjusted.

GetITDone

I like the Children idea however not sure it will work. I need scored Averages per Department of which there are several in the Department column...so I need to select only those departments ie: Project Management which are shatter through the 300 questions.

GetITDone

This might work! I will give this a try now and let you know. Thank you very much

Paul Newcome

Ah. Ok. Then this...

=SUMIFS(Yes:Yes, [Department - ie: Sales Eng, TDE's etc]:[Department - ie: Sales Eng, TDE's etc], "Project Management") / COUNTIFS(Yes:Yes, ISNUMBER(@cell), [Department - ie: Sales Eng, TDE's etc]:[Department - ie: Sales Eng, TDE's etc], "Project Management")

This will look at the entire column and only calculate for those that have the department as Project Management. Just replace the underlined portion with whatever department you are wanting to calculate for.

GetITDone

This is bizarre. The first 2 sections work fine with your formula. did not count the NA and scored correctly.

However for the next larger section, I am getting negative scores whenever I select NA. After changing them all the NA is gave me a -1000%. Any ideas would be appreciated. :-)

=SUM(Yes183, Yes206, Yes208, Yes228, Yes229, Yes230, Yes250, Yes252, Yes263, Yes291) / COUNT([Department - ie: Sales Eng, TDE's etc]183, [Department - ie: Sales Eng, TDE's etc]206, [Department - ie: Sales Eng, TDE's etc]208, [Department - ie: Sales Eng, TDE's etc]228, [Department - ie: Sales Eng, TDE's etc]229, [Department - ie: Sales Eng, TDE's etc]230, [Department - ie: Sales Eng, TDE's etc]250, [Department - ie: Sales Eng, TDE's etc]252, [Department - ie: Sales Eng, TDE's etc]263, [Department - ie: Sales Eng, TDE's etc]291) - (COUNTIF(Yes183, ="NA") + (COUNTIF(Yes206, ="NA") + (COUNTIF(Yes208, ="NA") + (COUNTIF(Yes228, ="NA") + (COUNTIF(Yes229, ="NA") + (COUNTIF(Yes230, ="NA") + (COUNTIF(Yes250, ="NA") + (COUNTIF(Yes252, ="NA") + (COUNTIF(Yes263, ="NA") + COUNTIF(Yes291, ="NA"))))))))))

Paul Newcome

To use the above formula, you will need to wrap the last bit in parenthesis. The basic order of operations is parenthesis, multiplication, division, addition, subtraction. Following this order, you are dividing the sum by the count THEN subtracting the N/A's. Try this formula. Not the added parenthesis after the / and at the end of the formula...

=SUM(Yes183, Yes206, Yes208, Yes228, Yes229, Yes230, Yes250, Yes252, Yes263, Yes291) / (COUNT([Department - ie: Sales Eng, TDE's etc]183, [Department - ie: Sales Eng, TDE's etc]206, [Department - ie: Sales Eng, TDE's etc]208, [Department - ie: Sales Eng, TDE's etc]228, [Department - ie: Sales Eng, TDE's etc]229, [Department - ie: Sales Eng, TDE's etc]230, [Department - ie: Sales Eng, TDE's etc]250, [Department - ie: Sales Eng, TDE's etc]252, [Department - ie: Sales Eng, TDE's etc]263, [Department - ie: Sales Eng, TDE's etc]291) - (COUNTIF(Yes183, ="NA") + (COUNTIF(Yes206, ="NA") + (COUNTIF(Yes208, ="NA") + (COUNTIF(Yes228, ="NA") + (COUNTIF(Yes229, ="NA") + (COUNTIF(Yes230, ="NA") + (COUNTIF(Yes250, ="NA") + (COUNTIF(Yes252, ="NA") + (COUNTIF(Yes263, ="NA") + COUNTIF(Yes291, ="NA")))))))))))

GetITDone

I copied but got UNPARSEABLE. :-(

I need all yes and no answers to be averaged per Installation Process, in this case 'Conference parent... for department "Project Management"

GetITDone

Hi Paul

I applied this however I change all the answers to Yes which provided 100% Great! But then I changed 5 out of the 10 answers to = NA. the score went down to 50% and needs to remain 100% because we do not want those NA calculated into the scores.

Paul Newcome

Try the below formula. It should simplify things greatly for you...

You are summing the Yes column if the department column = (in this example) Project Management. You are then dividing it by how many cells in the Yes column contain a number and are in the same row where the department is (in this example) Project Management.

GetITDone

Sorry I didn't have your formula in there . I put this one in but get Unparseable.

Paul Newcome

Your formula above has too many parenthesis in it. Try this version...

=SUM(Yes183, Yes206, Yes208, Yes228, Yes229, Yes230, Yes250, Yes252, Yes263, Yes291) / (COUNT([Department - ie: Sales Eng, TDE's etc]183, [Department - ie: Sales Eng, TDE's etc]206, [Department - ie: Sales Eng, TDE's etc]208, [Department - ie: Sales Eng, TDE's etc]228, [Department - ie: Sales Eng, TDE's etc]229, [Department - ie: Sales Eng, TDE's etc]230, [Department - ie: Sales Eng, TDE's etc]250, [Department - ie: Sales Eng, TDE's etc]252, [Department - ie: Sales Eng, TDE's etc]263, [Department - ie: Sales Eng, TDE's etc]291) - COUNTIF(Yes183, ="NA") + COUNTIF(Yes206, ="NA") + COUNTIF(Yes208, ="NA") + COUNTIF(Yes228, ="NA") + COUNTIF(Yes229, ="NA") + COUNTIF(Yes230, ="NA") + COUNTIF(Yes250, ="NA") + COUNTIF(Yes252, ="NA") + COUNTIF(Yes263, ="NA") + COUNTIF(Yes291, ="NA"))

Sign in to join the conversation!

Quick Links

Trending discussions

Dashboard Adding Widgets Spontaneously
Howdy, I am working on developing a several dashboards for our team, and for whatever reason the widgets I place are multiplying. I have copied/pasted some, but other I have just added I am ending up with dashboards that look like this: When originally I only had the top four items. It has happened on several different…
Project Management with tracking costs
I am a construction project manager and built sheets that include actual costs for individual tasks, departments and overall project. I update the costs cells as invoices come in by manually calculating then entering in new amount. Ex: Plumbing: week 1 $300, week 2 $250, week 3 $150 = $700. I enter $700 in cell. I'm…
Showing "stacked" data / Statuses of items by swimlane
I have multiple items that each have their own statuses and that roll up to "workstreams" or swimlanes. On the dashboard, I would like to show the progress of the items by swimlane without breaking it out into separate widgets for each. This would ideally be a stacked bar where each bar represented the Items and was broken…