Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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") |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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") |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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") |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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") |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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") |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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") |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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") |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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") |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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") |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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") |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
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") |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
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") |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
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") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNTIF with multiple conditions? | Excel Worksheet Functions | |||
COUNTIF with multiple conditions | Excel Discussion (Misc queries) | |||
COUNTIF Multiple Conditions | Excel Discussion (Misc queries) | |||
COUNTIF and multiple conditions | Excel Worksheet Functions | |||
COUNTIF - multiple conditions | Excel Worksheet Functions |