Number of cells that have same values

Imagine there are two columns of cells, each row of two cells are
corresponding to each other. How to find out the number of rows where the two
corresponding cells have the same value?

Assume your Columns are A and B, and your rows go down to 20, Array enter
this formula (CTRL+SHIFT+ENTER):

=SUM(IF(A1:A20=B1:B20,1,0))

Change the column and row references to fit your data.

--
Regards,
Dave


"Lingyan Hu" wrote:

> Imagine there are two columns of cells, each row of two cells are
> corresponding to each other. How to find out the number of rows where the two
> corresponding cells have the same value?

=SUMPRODUCT(--(A2:A20=B2:B20),--(A2:A20<>""),--(B2:B20<>""))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Lingyan Hu" <Lingyan Hu@discussions.microsoft.com> wrote in message
news:CD5AB162-B95B-4802-8C65-8FCA3E5A8263@microsoft.com...
> Imagine there are two columns of cells, each row of two cells are
> corresponding to each other. How to find out the number of rows where the
two
> corresponding cells have the same value?

Shall I push (CTRL+SHIFT+ENTER) at the same time while entering the formula?
I tried, but it doesn't seem to work.

"David Billigmeier" wrote:

> Assume your Columns are A and B, and your rows go down to 20, Array enter
> this formula (CTRL+SHIFT+ENTER):
>
> =SUM(IF(A1:A20=B1:B20,1,0))
>
> Change the column and row references to fit your data.
>
> --
> Regards,
> Dave
>
>
> "Lingyan Hu" wrote:
>
> > Imagine there are two columns of cells, each row of two cells are
> > corresponding to each other. How to find out the number of rows where the two
> > corresponding cells have the same value?

Type this formula in the formula bar, and instead of just pushing "Enter" to
commit it, push CTRL+SHIFT+ENTER.

Also, after looking at Bob's post I realize my formula isn't taking into
account blank cells, change to the following to fix (or just use Bob's
formula):

=SUM(IF(((A1:A20=B1:B20)*(A1:A20<>"")*(B1:B20<>"")),1,0))

--
Regards,
Dave


"Lingyan Hu" wrote:

> Shall I push (CTRL+SHIFT+ENTER) at the same time while entering the formula?
> I tried, but it doesn't seem to work.
>
> "David Billigmeier" wrote:
>
> > Assume your Columns are A and B, and your rows go down to 20, Array enter
> > this formula (CTRL+SHIFT+ENTER):
> >
> > =SUM(IF(A1:A20=B1:B20,1,0))
> >
> > Change the column and row references to fit your data.
> >
> > --
> > Regards,
> > Dave
> >
> >
> > "Lingyan Hu" wrote:
> >
> > > Imagine there are two columns of cells, each row of two cells are
> > > corresponding to each other. How to find out the number of rows where the two
> > > corresponding cells have the same value?

Dave,

Your version can do away with the IF test

=SUM(((A1:A20=B1:B20)*(A1:A20<>"")*(B1:B20<>"")))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"David Billigmeier" <DavidBilligmeier@discussions.microsoft.com> wrote in
message news:C1E72DAF-CD9E-4E67-A15A-9A9B251896A1@microsoft.com...
> Type this formula in the formula bar, and instead of just pushing "Enter"
to
> commit it, push CTRL+SHIFT+ENTER.
>
> Also, after looking at Bob's post I realize my formula isn't taking into
> account blank cells, change to the following to fix (or just use Bob's
> formula):
>
> =SUM(IF(((A1:A20=B1:B20)*(A1:A20<>"")*(B1:B20<>"")),1,0))
>
> --
> Regards,
> Dave
>
>
> "Lingyan Hu" wrote:
>
> > Shall I push (CTRL+SHIFT+ENTER) at the same time while entering the
formula?
> > I tried, but it doesn't seem to work.
> >
> > "David Billigmeier" wrote:
> >
> > > Assume your Columns are A and B, and your rows go down to 20, Array
enter
> > > this formula (CTRL+SHIFT+ENTER):
> > >
> > > =SUM(IF(A1:A20=B1:B20,1,0))
> > >
> > > Change the column and row references to fit your data.
> > >
> > > --
> > > Regards,
> > > Dave
> > >
> > >
> > > "Lingyan Hu" wrote:
> > >
> > > > Imagine there are two columns of cells, each row of two cells are
> > > > corresponding to each other. How to find out the number of rows
where the two
> > > > corresponding cells have the same value?

Very true, there are many ways to word it.


--
Regards,
Dave


"Bob Phillips" wrote:

> Dave,
>
> Your version can do away with the IF test
>
> =SUM(((A1:A20=B1:B20)*(A1:A20<>"")*(B1:B20<>"")))
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "David Billigmeier" <DavidBilligmeier@discussions.microsoft.com> wrote in
> message news:C1E72DAF-CD9E-4E67-A15A-9A9B251896A1@microsoft.com...
> > Type this formula in the formula bar, and instead of just pushing "Enter"
> to
> > commit it, push CTRL+SHIFT+ENTER.
> >
> > Also, after looking at Bob's post I realize my formula isn't taking into
> > account blank cells, change to the following to fix (or just use Bob's
> > formula):
> >
> > =SUM(IF(((A1:A20=B1:B20)*(A1:A20<>"")*(B1:B20<>"")),1,0))
> >
> > --
> > Regards,
> > Dave
> >
> >
> > "Lingyan Hu" wrote:
> >
> > > Shall I push (CTRL+SHIFT+ENTER) at the same time while entering the
> formula?
> > > I tried, but it doesn't seem to work.
> > >
> > > "David Billigmeier" wrote:
> > >
> > > > Assume your Columns are A and B, and your rows go down to 20, Array
> enter
> > > > this formula (CTRL+SHIFT+ENTER):
> > > >
> > > > =SUM(IF(A1:A20=B1:B20,1,0))
> > > >
> > > > Change the column and row references to fit your data.
> > > >
> > > > --
> > > > Regards,
> > > > Dave
> > > >
> > > >
> > > > "Lingyan Hu" wrote:
> > > >
> > > > > Imagine there are two columns of cells, each row of two cells are
> > > > > corresponding to each other. How to find out the number of rows
> where the two
> > > > > corresponding cells have the same value?
>
>
>