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

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

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

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!

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

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

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

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

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