Count function

Carol-Anne Cerbone

Hi

I need help with a formula. I have a column that I want to count but only based on a values in a different column. I have tried the following formula but it's not working and I can't figure out what I am doing wrong.

=countif([Test Script #]:[Test Script #], [Pass ?]:[Pass ?], <> "N/A")

The Test Script # column is the column I'm trying to count and the Pass ? is the column that has the values of either Yes, No or N/A. If the Pass ? has a value of N/A then I don't want it in the count.

Make sense?

ker9

@Carol-Anne Cerbone

How about this?

=COUNT([Test Script #]:[Test Script #]) - COUNTIF([Pass?]:[Pass?], ="N/A")

ker9

Hi@Carol-Anne Cerbone

I think you need to remove the comma before the <>

=COUNTIF([Test Script #]:[Test Script #], [Pass ?]:[Pass ?] <> "N/A")

Hope this helps!

Carol-Anne Cerbone

Thanks, that removed the error message but it is still counting the N/A in the total count. Any other suggestions? Below is the updated formula

=COUNTIF([Test Script #]:[Test Script #], [Pass?]:[Pass?] <> "N/A")

I also tried

=COUNTIFS([Test Script #]:[Test Script #], [Pass?]:[Pass?] <> "N/A")

ker9

@Carol-Anne Cerbone-

Try this instead:

=COUNTIFS([Test Script #]:[Test Script #], <>"", [Pass?]:[Pass?], <>"N/A")

This may not count properly if the Pass? column is blank - do we need to consider that?

Can you count just the items in the Pass column?

Carol-Anne Cerbone

@ker9- that didn't work either. Any other suggestions?

ker9

@Carol-Anne Cerbone

Can you provide detail of what type of data is in your columns or a screen shot?

Is there data in the [Pass?] column for each item in the [Test Script #] column?

Carol-Anne Cerbone

The column type for Test Script # is a auto generated number system and the Pass column is a drop down list

ker9

@Carol-Anne Cerbone

How about this?

=COUNT([Test Script #]:[Test Script #]) - COUNTIF([Pass?]:[Pass?], ="N/A")

Carol-Anne Cerbone

That worked!!!! Thank you so much