Home |
Search |
Today's Posts |
#1
|
|||
|
|||
countif condition problem
I am working with two rows of data, I want to count the number of times that
both rows are equal to a value I specify. Something like this COUNTIF(1:1, AND(1:X="1", 2:X="0")). Given the table below, the formula should be equal to two. A B C D E F G H I J K 1 0 0 1 0 0 1 0 0 1 0 1 2 1 0 0 1 0 1 0 0 0 1 1 -- Scott Miller University of Washington Chemistry |
#2
|
|||
|
|||
countif condition problem
Try...
=SUMPRODUCT(--(A1:K1=1),--(A2:K2=0)) Note that SUMPRODUCT does not accept whole column references. Hope this helps! In article , "Scott" wrote: I am working with two rows of data, I want to count the number of times that both rows are equal to a value I specify. Something like this COUNTIF(1:1, AND(1:X="1", 2:X="0")). Given the table below, the formula should be equal to two. A B C D E F G H I J K 1 0 0 1 0 0 1 0 0 1 0 1 2 1 0 0 1 0 1 0 0 0 1 1 |
#3
|
|||
|
|||
countif condition problem
=SUMPRODUCT(--(1:1=1),--(2:2=0),--(2:2<""))
-- HTH RP (remove nothere from the email address if mailing direct) "Scott" wrote in message ... I am working with two rows of data, I want to count the number of times that both rows are equal to a value I specify. Something like this COUNTIF(1:1, AND(1:X="1", 2:X="0")). Given the table below, the formula should be equal to two. A B C D E F G H I J K 1 0 0 1 0 0 1 0 0 1 0 1 2 1 0 0 1 0 1 0 0 0 1 1 -- Scott Miller University of Washington Chemistry |
#4
|
|||
|
|||
countif condition problem
....but as Bob has shown, it does accept whole row references. Also, I
should have included a third argument, like Bob did, to deal with blank cells. In article , Domenic wrote: Try... =SUMPRODUCT(--(A1:K1=1),--(A2:K2=0)) Note that SUMPRODUCT does not accept whole column references. Hope this helps! |
#5
|
|||
|
|||
countif condition problem
Rather than speculate, I will ask, why do you need the 2:2<""? I tried this
out, and found you do need it (if there is a 1 in row 1,) but if I say anything else, I will be speculating. "Bob Phillips" wrote: =SUMPRODUCT(--(1:1=1),--(2:2=0),--(2:2<"")) -- HTH RP (remove nothere from the email address if mailing direct) "Scott" wrote in message ... I am working with two rows of data, I want to count the number of times that both rows are equal to a value I specify. Something like this COUNTIF(1:1, AND(1:X="1", 2:X="0")). Given the table below, the formula should be equal to two. A B C D E F G H I J K 1 0 0 1 0 0 1 0 0 1 0 1 2 1 0 0 1 0 1 0 0 0 1 1 -- Scott Miller University of Washington Chemistry |
#6
|
|||
|
|||
countif condition problem
It's because blank cells return zero when evaluated thus to avoid blank
cells skewing the result when 0 is a condition one can either check for number like in =ISNUMBER(Range) or not empty =Range<"" -- Regards, Peo Sjoblom "Kleev" wrote in message ... Rather than speculate, I will ask, why do you need the 2:2<""? I tried this out, and found you do need it (if there is a 1 in row 1,) but if I say anything else, I will be speculating. "Bob Phillips" wrote: =SUMPRODUCT(--(1:1=1),--(2:2=0),--(2:2<"")) -- HTH RP (remove nothere from the email address if mailing direct) "Scott" wrote in message ... I am working with two rows of data, I want to count the number of times that both rows are equal to a value I specify. Something like this COUNTIF(1:1, AND(1:X="1", 2:X="0")). Given the table below, the formula should be equal to two. A B C D E F G H I J K 1 0 0 1 0 0 1 0 0 1 0 1 2 1 0 0 1 0 1 0 0 0 1 1 -- Scott Miller University of Washington Chemistry |
#7
|
|||
|
|||
countif condition problem
Thanks for clearing that up.
"Peo Sjoblom" wrote: It's because blank cells return zero when evaluated thus to avoid blank cells skewing the result when 0 is a condition one can either check for number like in =ISNUMBER(Range) or not empty =Range<"" -- Regards, Peo Sjoblom "Kleev" wrote in message ... Rather than speculate, I will ask, why do you need the 2:2<""? I tried this out, and found you do need it (if there is a 1 in row 1,) but if I say anything else, I will be speculating. "Bob Phillips" wrote: =SUMPRODUCT(--(1:1=1),--(2:2=0),--(2:2<"")) -- HTH RP (remove nothere from the email address if mailing direct) "Scott" wrote in message ... I am working with two rows of data, I want to count the number of times that both rows are equal to a value I specify. Something like this COUNTIF(1:1, AND(1:X="1", 2:X="0")). Given the table below, the formula should be equal to two. A B C D E F G H I J K 1 0 0 1 0 0 1 0 0 1 0 1 2 1 0 0 1 0 1 0 0 0 1 1 -- Scott Miller University of Washington Chemistry |
#8
|
|||
|
|||
countif condition problem
i have a similar problem... here is my situation OK i got these hours A B TED 14 BOB 23 GARY 32 LEW 15 Schedule errors: 1 Lets say that Gary and Bob are full time employees..so i need to make sure i schedule them at least 32 hours I need a function that will report in number format how many "schedule conflits" or "schedule errors i have" i have meaning i have a full timer only scheduled 20 hours. i tried using =COUNTIF(B1:B4,"=0")-COUNTIF(B1:B4,"32") Problem i had with that is it red flagged ANY employe who was not at 32 hours what i need it to do is ONLY look at say cell B2,B5,B10 and check to see if they are at least 32 hours. ...Hope this makes sense..thanks again -- mjman15 ------------------------------------------------------------------------ mjman15's Profile: http://www.excelforum.com/member.php...o&userid=28720 View this thread: http://www.excelforum.com/showthread...hreadid=484005 |
#9
|
|||
|
|||
countif condition problem
Best to add another column with a flag to say full-timer or not, and then
use =SUMPRODUCT(--(C:C="Y"),--(B:B<32))&" people under-scheduled" -- HTH RP (remove nothere from the email address if mailing direct) "mjman15" wrote in message ... i have a similar problem... here is my situation OK i got these hours A B TED 14 BOB 23 GARY 32 LEW 15 Schedule errors: 1 Lets say that Gary and Bob are full time employees..so i need to make sure i schedule them at least 32 hours I need a function that will report in number format how many "schedule conflits" or "schedule errors i have" i have meaning i have a full timer only scheduled 20 hours. i tried using =COUNTIF(B1:B4,"=0")-COUNTIF(B1:B4,"32") Problem i had with that is it red flagged ANY employe who was not at 32 hours what i need it to do is ONLY look at say cell B2,B5,B10 and check to see if they are at least 32 hours. ...Hope this makes sense..thanks again -- mjman15 ------------------------------------------------------------------------ mjman15's Profile: http://www.excelforum.com/member.php...o&userid=28720 View this thread: http://www.excelforum.com/showthread...hreadid=484005 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
countif problem | Excel Discussion (Misc queries) | |||
Excel Display Problem | Excel Discussion (Misc queries) | |||
Combining IF and COUNTIF based on two columns | Excel Discussion (Misc queries) | |||
Countif - Countif | Excel Worksheet Functions | |||
Countif Problem | Excel Worksheet Functions |