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
Try...
=SUMPRODUCT(--(A1:K1=1),--(A2:K2=0))
Note that SUMPRODUCT does not accept whole column references.
Hope this helps!
In article <[email protected]>,
"Scott" <[email protected]> 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
=SUMPRODUCT(--(1:1=1),--(2:2=0),--(2:2<>""))
--
HTH
RP
(remove nothere from the email address if mailing direct)
"Scott" <[email protected]> wrote in message
news:[email protected]...
> 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
....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 <[email protected]>,
Domenic <[email protected]> wrote:
> Try...
>
> =SUMPRODUCT(--(A1:K1=1),--(A2:K2=0))
>
> Note that SUMPRODUCT does not accept whole column references.
>
> Hope this helps!
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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>
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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> >
> >
> >
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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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
> > >
> > >
> > >
>
>
>
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
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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks