Number of cells that have same values

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?

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?
>
>
>