Countifs not calculating as expected

GetITDone

It only brought (false) results when I put in with 1 less end bracket.   when I change to NA the score goes into the minuses still. 

 

=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

Because you are back to the problem of dividing the sum by the count THEN subtracting the first N/A THEN adding the rest of the N/A's.

 

=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")))

 

Try this...

GetITDone

Hi Paul,

This is working!! I so very much appreciate your assistance and time!!  :-)

What part in this is actually solving the problem of dividing the sum by the count then subtracting the first NA, then adding the rest?  

Now I need to apply this to many other department as such. These departments are a sub total of which I will avg all of the same departments at the very top of my sheet.  I will work on that too, so long as I keep this same formula, I assume it will also work.

GetITDone

So I am obviously new at this.  But dumb question.  How come I can get this first part to give me results (albeit not the correct results) but result non the less but when I put the last bit in it I get unparseable?

=SUM(Yes332, Yes333, Yes334, Yes336, Yes339, Yes340) / (COUNT([Department - ie: Sales Eng, TDE's etc]332, [Department - ie: Sales Eng, TDE's etc]333, [Department - ie: Sales Eng, TDE's etc]334, [Department - ie: Sales Eng, TDE's etc]336, [Department - ie: Sales Eng, TDE's etc]339, [Department - ie: Sales Eng, TDE's etc]340)

- (COUNTIF(Yes332, ="NA") + COUNTIF(Yes333, ="NA") + COUNTIF(Yes334, ="NA") + COUNTIF(Yes336, ="NA") + COUNTIF(Yes339, ="NA") + COUNTIF(Yes340, ="NA")))

Paul Newcome

The way this is supposed to work is in a few different stages. First it adds all of your N/A's together. Then it Counts how many cells you are looking at. Then it subtracts the N/A's from your count. The number that is left is what the Yes's is divided by.

Paul Newcome

I am going to suggest breaking this down into sections for testing because you are using so many specific cell references. In one cell put

=SUM(Yes332, Yes333, Yes334, Yes336, Yes339, Yes340)

In another cell

=COUNT([Department - ie: Sales Eng, TDE's etc]332, [Department - ie: Sales Eng, TDE's etc]333, [Department - ie: Sales Eng, TDE's etc]334, [Department - ie: Sales Eng, TDE's etc]336, [Department - ie: Sales Eng, TDE's etc]339, [Department - ie: Sales Eng, TDE's etc]340)

And in another

=COUNTIF(Yes332, ="NA") + COUNTIF(Yes333, ="NA") + COUNTIF(Yes334, ="NA") + COUNTIF(Yes336, ="NA") + COUNTIF(Yes339, ="NA") + COUNTIF(Yes340, ="NA")

.

Make sure each section works as is. Once everything is working on its own, then combine them. Just remember the order of operations. Parenthesis, exponents, multiplication, division, addition, subtraction.

So the first step is getting your count of N/A's. Since you want to do that first, you will want to wrap it in parenthesis:

=(COUNTIF(Yes332, ="NA") + COUNTIF(Yes333, ="NA") + COUNTIF(Yes334, ="NA") + COUNTIF(Yes336, ="NA") + COUNTIF(Yes339, ="NA") + COUNTIF(Yes340, ="NA"))

.

Next you want to count how many cells you are actually looking at. Since you have that in a single formula it is already wrapped in parenthesis. Therefore you don't need to adjust that section and can just move on to the next.

Now you want to subtract the N/A's from the count, so you'll want to wrap that in parenthesis.

=(COUNT([Department - ie: Sales Eng, TDE's etc]332, [Department - ie: Sales Eng, TDE's etc]333, [Department - ie: Sales Eng, TDE's etc]334, [Department - ie: Sales Eng, TDE's etc]336, [Department - ie: Sales Eng, TDE's etc]339, [Department - ie: Sales Eng, TDE's etc]340) - (COUNTIF(Yes332, ="NA") + COUNTIF(Yes333, ="NA") + COUNTIF(Yes334, ="NA") + COUNTIF(Yes336, ="NA") + COUNTIF(Yes339, ="NA") + COUNTIF(Yes340, ="NA")))

.

This will give you the number that you want to divide your Yes's by, so you end up with...

=SUM(Yes332, Yes333, Yes334, Yes336, Yes339, Yes340) / (COUNT([Department - ie: Sales Eng, TDE's etc]332, [Department - ie: Sales Eng, TDE's etc]333, [Department - ie: Sales Eng, TDE's etc]334, [Department - ie: Sales Eng, TDE's etc]336, [Department - ie: Sales Eng, TDE's etc]339, [Department - ie: Sales Eng, TDE's etc]340) - (COUNTIF(Yes332, ="NA") + COUNTIF(Yes333, ="NA") + COUNTIF(Yes334, ="NA") + COUNTIF(Yes336, ="NA") + COUNTIF(Yes339, ="NA") + COUNTIF(Yes340, ="NA")))

GetITDone

Breaking it down was for sure a good way to troubleshoot, thanks for that!

Paul Newcome

Happy to help. 

GetITDone

HI    I have been going cross eyed with this one.  It all looks correct however I am getting 102% and goes higher the more NA I select.  I get unparseable when I add a third bracket at the end of all this.  Where am I going wrong.  This is the same formula as the one above. 

 

=SUM(Yes179, Yes180, Yes181, Yes182, Yes187, Yes187, Yes188, Yes191, Yes192, Yes195, Yes232, Yes234, Yes235, Yes236, Yes237, Yes239, Yes240, Yes242, Yes243, Yes244, Yes245, Yes253, Yes254, Yes255, Yes259, Yes260, Yes279, Yes281, Yes284, Yes285, Yes286, Yes288, Yes289, Yes290, Yes197, Yes199, Yes200, Yes201, Yes209, Yes210, Yes211, Yes215, Yes216, Yes221, Yes231) / (COUNT([Department - ie: Sales Eng, TDE's etc]179, [Department - ie: Sales Eng, TDE's etc]180, [Department - ie: Sales Eng, TDE's etc]181, [Department - ie: Sales Eng, TDE's etc]182, [Department - ie: Sales Eng, TDE's etc]187, [Department - ie: Sales Eng, TDE's etc]188, [Department - ie: Sales Eng, TDE's etc]191, [Department - ie: Sales Eng, TDE's etc]192, [Department - ie: Sales Eng, TDE's etc]195, [Department - ie: Sales Eng, TDE's etc]197, [Department - ie: Sales Eng, TDE's etc]199, [Department - ie: Sales Eng, TDE's etc]200, [Department - ie: Sales Eng, TDE's etc]201, [Department - ie: Sales Eng, TDE's etc]209, [Department - ie: Sales Eng, TDE's etc]210, [Department - ie: Sales Eng, TDE's etc]211, [Department - ie: Sales Eng, TDE's etc]215, [Department - ie: Sales Eng, TDE's etc]216, [Department - ie: Sales Eng, TDE's etc]221, [Department - ie: Sales Eng, TDE's etc]231, [Department - ie: Sales Eng, TDE's etc]232, [Department - ie: Sales Eng, TDE's etc]234, [Department - ie: Sales Eng, TDE's etc]235, [Department - ie: Sales Eng, TDE's etc]236, [Department - ie: Sales Eng, TDE's etc]237, [Department - ie: Sales Eng, TDE's etc]239, [Department - ie: Sales Eng, TDE's etc]240, [Department - ie: Sales Eng, TDE's etc]242, [Department - ie: Sales Eng, TDE's etc]243, [Department - ie: Sales Eng, TDE's etc]244, [Department - ie: Sales Eng, TDE's etc]245, [Department - ie: Sales Eng, TDE's etc]253, [Department - ie: Sales Eng, TDE's etc]254, [Department - ie: Sales Eng, TDE's etc]255, [Department - ie: Sales Eng, TDE's etc]259, [Department - ie: Sales Eng, TDE's etc]260, [Department - ie: Sales Eng, TDE's etc]279, [Department - ie: Sales Eng, TDE's etc]281, [Department - ie: Sales Eng, TDE's etc]284, [Department - ie: Sales Eng, TDE's etc]285, [Department - ie: Sales Eng, TDE's etc]286, [Department - ie: Sales Eng, TDE's etc]288, [Department - ie: Sales Eng, TDE's etc]289, [Department - ie: Sales Eng, TDE's etc]290) - (COUNTIF(Yes179, ="NA") + COUNTIF(Yes180, ="NA") + COUNTIF(Yes181, ="NA") + COUNTIF(Yes182, ="NA") + COUNTIF(Yes187, ="NA") + COUNTIF(Yes188, ="NA") + COUNTIF(Yes191, ="NA") + COUNTIF(Yes192, ="NA") + COUNTIF(Yes195, ="NA") + COUNTIF(Yes232, ="NA") + COUNTIF(Yes235, ="NA") + COUNTIF(Yes236, ="NA") + COUNTIF(Yes237, ="NA") + COUNTIF(Yes239, ="NA") + COUNTIF(Yes240, ="NA") + COUNTIF(Yes242, ="NA") + COUNTIF(Yes243, ="NA") + COUNTIF(Yes244, ="NA") + COUNTIF(Yes245, ="NA") + COUNTIF(Yes253, ="NA") + COUNTIF(Yes254, ="NA") + COUNTIF(Yes255, ="NA") + COUNTIF(Yes260, ="NA") + COUNTIF(Yes279, ="NA") + COUNTIF(Yes281, ="NA") + COUNTIF(Yes284, ="NA")) + COUNTIF(Yes285, ="NA") + COUNTIF(Yes286, ="NA") + COUNTIF(Yes288, ="NA") + COUNTIF(Yes289, ="NA") + COUNTIF(Yes290, ="NA") + COUNTIF(Yes197, ="NA") + COUNTIF(Yes199, ="NA") + COUNTIF(Yes200, ="NA") + COUNTIF(Yes201, ="NA") + COUNTIF(Yes209, ="NA") + COUNTIF(Yes210, ="NA") + COUNTIF(Yes211, ="NA") + COUNTIF(Yes215, ="NA") + COUNTIF(Yes216, ="NA") + COUNTIF(Yes221, ="NA") + COUNTIF(Yes231, ="NA"))

GetITDone

I fixed that countif with 284 removed the extra bracket but still no change.

Paul Newcome

Double check that all cells are referenced in all three parts. I did a quick count, and I came up with a different number of cells in each part.

I very strongly recommend working in a checkbox column and using a formula to automate it based on your criteria.

You can then run a basic countifs statement to pull how many boxes are checked.

 

While it may require a little more work to set up, it will make your overall calculations much more efficient and accurate in the end.