Countif with Multiple Not Conditions
 

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

Countif with Multiple Not Conditions
 

Look at SUMPRODUCT:

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

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

HTH

"Chart_Maker_Wonderer" wrote:

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

Countif with Multiple Not Conditions
 

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?

"Toppers" wrote:

Look at SUMPRODUCT:

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

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

HTH

"Chart_Maker_Wonderer" wrote:

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

Countif with Multiple Not Conditions
 

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

Why not try it?

"Chart_Maker_Wonderer" wrote:

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?

"Toppers" wrote:

Look at SUMPRODUCT:

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

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

HTH

"Chart_Maker_Wonderer" wrote:

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

Countif with Multiple Not Conditions
 

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.

"Toppers" wrote:

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

Why not try it?

"Chart_Maker_Wonderer" wrote:

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?

"Toppers" wrote:

Look at SUMPRODUCT:

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

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

HTH

"Chart_Maker_Wonderer" wrote:

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

Countif with Multiple Not Conditions
 

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?



"Chart_Maker_Wonderer" wrote:

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.

"Toppers" wrote:

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

Why not try it?

"Chart_Maker_Wonderer" wrote:

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?

"Toppers" wrote:

Look at SUMPRODUCT:

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

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

HTH

"Chart_Maker_Wonderer" wrote:

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

Countif with Multiple Not Conditions
 

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.


"Toppers" wrote:


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?



"Chart_Maker_Wonderer" wrote:

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.

"Toppers" wrote:

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

Why not try it?

"Chart_Maker_Wonderer" wrote:

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?

"Toppers" wrote:

Look at SUMPRODUCT:

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

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

HTH

"Chart_Maker_Wonderer" wrote:

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

Countif with Multiple Not Conditions
 

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?


"Chart_Maker_Wonderer" wrote:

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.


"Toppers" wrote:


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?



"Chart_Maker_Wonderer" wrote:

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.

"Toppers" wrote:

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

Why not try it?

"Chart_Maker_Wonderer" wrote:

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?

"Toppers" wrote:

Look at SUMPRODUCT:

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

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

HTH

"Chart_Maker_Wonderer" wrote:

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

Countif with Multiple Not Conditions
 

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

"Toppers" wrote:

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?


"Chart_Maker_Wonderer" wrote:

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.


"Toppers" wrote:


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?



"Chart_Maker_Wonderer" wrote:

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.

"Toppers" wrote:

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

Why not try it?

"Chart_Maker_Wonderer" wrote:

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?

"Toppers" wrote:

Look at SUMPRODUCT:

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

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

HTH

"Chart_Maker_Wonderer" wrote:

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

Countif with Multiple Not Conditions
 

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.


"Chart_Maker_Wonderer" wrote:

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

"Toppers" wrote:

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?


"Chart_Maker_Wonderer" wrote:

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.


"Toppers" wrote:


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?



"Chart_Maker_Wonderer" wrote:

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.

"Toppers" wrote:

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

Why not try it?

"Chart_Maker_Wonderer" wrote:

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?

"Toppers" wrote:

Look at SUMPRODUCT:

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

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

HTH

"Chart_Maker_Wonderer" wrote:

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

Countif with Multiple Not Conditions
 

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.

"Toppers" wrote:

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.


"Chart_Maker_Wonderer" wrote:

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

"Toppers" wrote:

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?


"Chart_Maker_Wonderer" wrote:

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.


"Toppers" wrote:


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?



"Chart_Maker_Wonderer" wrote:

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.

"Toppers" wrote:

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

Why not try it?

"Chart_Maker_Wonderer" wrote:

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?

"Toppers" wrote:

Look at SUMPRODUCT:

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

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

HTH

"Chart_Maker_Wonderer" wrote:

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

Countif with Multiple Not Conditions
 

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

"Toppers" wrote:

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.


"Chart_Maker_Wonderer" wrote:

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

"Toppers" wrote:

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?


"Chart_Maker_Wonderer" wrote:

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.


"Toppers" wrote:


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?



"Chart_Maker_Wonderer" wrote:

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.

"Toppers" wrote:

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

Why not try it?

"Chart_Maker_Wonderer" wrote:

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?

"Toppers" wrote:

Look at SUMPRODUCT:

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

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

HTH

"Chart_Maker_Wonderer" wrote:

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

Countif with Multiple Not Conditions
 

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

"Chart_Maker_Wonderer" wrote:

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

"Toppers" wrote:

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.


"Chart_Maker_Wonderer" wrote:

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

"Toppers" wrote:

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?


"Chart_Maker_Wonderer" wrote:

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.


"Toppers" wrote:


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?



"Chart_Maker_Wonderer" wrote:

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.

"Toppers" wrote:

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

Why not try it?

"Chart_Maker_Wonderer" wrote:

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?

"Toppers" wrote:

Look at SUMPRODUCT:

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

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

HTH

"Chart_Maker_Wonderer" wrote:

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