Countif with Multiple Not Conditions

  • Thread starter Chart_Maker_Wonderer
  • Start date
C

Chart_Maker_Wonderer

Is there a way that I can use OR and NOT statements in Countif statements?

I am working on an inventory sheet where it will count how many stations do
not working.

To determine that the station is good I have some different text, and if the
station is not working I have other text the person can type.

What I would like to do is to have Countif(a5:a7<>"Good
Monitor",b5:b7<>"Good Keyboard,B5:b7<>"So So Keyboard")
 
T

Toppers

Look at SUMPRODUCT:

=SUMPRODUCT(--(a5:a7 said:
Monitor"),--(b5:b7<>"Good Keyboard,"),--(B5:b7<>"So So Keyboard"))
Click to expand...

The -- coercises the TRUE/FALSE conditions to 1/0.

HTH
 
C

Chart_Maker_Wonderer

Will that produce a result that if I put that in a spreadsheet and if there
are 5 rows that do not have any of the stuff I am putting into the formula it
will return a value of 5?
 
T

Toppers

If all entries are blank for "n" rows you will get "n" as an answer.

Why not try it?
 
C

Chart_Maker_Wonderer

I am currently trying it and it looks like it will only return the number of
times that the conditions are matched in the first column of calculations.
 
T

Toppers

There was a typo im my formula .. an extraneous "," in the second parameter
{"Good Keyboard,"} but if I have the following in A2:B7

A3="Good Monitor"
B4="Good Keyboard"
B6="So so keyboard"

I get a result of 3 i.e. Rows 2,5 and 7 are blank

Is this correct?
 
C

Chart_Maker_Wonderer

if the formula you gave me was:

SUMPRODUCT(--(a5:a7<>"Good Monitor"),--(b5:b7<>"Good
Keyboard,"),--(B5:b7<>"So So Keyboard"))

Where would I get rid of the extra ","

For the Monitor I have more conditions that i do not want the line to be and
I was just duplicating what you had and it was working until I got to a
different column.
 
T

Toppers

Just delete it (the comma) from the formula!

=SUMPRODUCT(--(a5:a7<>"Good Monitor"),--(b5:b7<>"Good
Keyboard"),--(B5:b7<>"So So Keyboard"))

What happens with the different column? what is your "new" formula?
 
C

Chart_Maker_Wonderer

This is taken from the sheet I am working on. The columns that I am working
on are from f-I and the length of the columns are 6-232

=SUMPRODUCT(--(F6:F232<>""),--(F6:F232<>"Pixels Damaged"),--(F6:F232<>"Low
Resolution"),--(G6:G232<>""),--(G6:G232<>"USB Damaged"),--(G6:G232<>"PS2
Damaged"),--(G6:G232<>"Has PS2 Keyboard"))
 
T

Toppers

If, in any row, column F or G is blank you will get a result of zero for that
row.

I presume your requirement is if any of F or G or H or I are not equal to
the specified conditions for each column AND ALL are not blank, then the
count is 1 for that row.

i THINK (no sure) this might do what you want - add in conditions for H & I
but exclude test for blank as this done by end part of the formula.

=SUMPRODUCT(--($F$6:$F$232<>"Pixels Damaged"),--($F$6:$F$232<>"Low
Resolution"),--($G$6:$G$232<>"USB Damaged"),--($G$6:$G$232<>"PS2
Damaged"),--($G$6:$G$232<>"Has PS2
Keyboard"))-SUMPRODUCT((F6:F232="")*(G6:G232="")*(H6:H232="")*(I6:I232=""))

Sorry but my mind has gone blank on this ! I'll give it further thought later.
 
C

Chart_Maker_Wonderer

I am using the formula the count the number of stations that do not work. And
the things that I am checking for in this formula to see if it is not, all
fall under things that identify the station as working. I would like this
formula to check all the rows and columns for the equipment and if 1 row (is
1 station) has 1 or more pieces that would result in true it would just
result in 1 for that full row.

Thanks for the help.
 
C

Chart_Maker_Wonderer

Here is the full code that I made for the full line that I want to check. I
think I might of typed the last parts wrong because it is off by a few
numbers, but looking at the code I thought I typed it right.

=SUMPRODUCT(--($F$6:$F$232<>"Pixels Damaged"),--($F$6:$F$232<>"Low
Resolution"),--($G$6:$G$232<>"USB Damaged"),--($G$6:$G$232<>"PS2
Resolution"),--($G$6:$G$232<>"USB Damaged"),--($G$6:$G$232<>"PS2
Damaged"),--($G$6:$G$232<>"Has PS2 Keyboard"),--($H$6:$H$232<>"USB
Broken"),--($H$6:$H$232<>"PS2
Broken"),--($I$6:$I$232<>"Cracked"),--($J$6:$J$232<>"Broken"),--($J$6:$J$232<>"Pixels
Damaged"),--($K$6:$K$232<>"Damaged"),--($L$6:$L$232<>"Detached"),--($M$6:$M$232<>"Detached"))-SUMPRODUCT(($F$6:$F$232="")*($G$6:G232="")*($H$6:$H$232="")*($I$6:$I$232="")*($J$6:$J$232="")*($K$6:$K$232="")*($L$6:$L$232="")*($M$6:$M$232=""))
 
C

Chart_Maker_Wonderer

Never Mind. I have the full code typed out and working good now. Thanks for
the help.
 
Top