|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
December 8th, 2005, 04:38 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
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? 
|
|
#2
December 8th, 2005, 04:46 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
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?
|
|
#3
December 8th, 2005, 04:47 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Number of cells that have same values
=SUMPRODUCT(--(A2:A20=B2:B20),--(A2:A20""),--(B2:B20""))
-- HTH RP (remove nothere from the email address if mailing direct) "Lingyan Hu" Lingyan wrote in message ... 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?
|
|
#4
December 8th, 2005, 04:57 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Number of cells that have same values
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?
|
|
#5
December 8th, 2005, 05:05 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Number of cells that have same values
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?
|
|
#6
December 8th, 2005, 05:21 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Number of cells that have same values
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" wrote in message ... 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?
|
|
#7
December 8th, 2005, 05:37 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Number of cells that have same values
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" wrote in message ... 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?
|
| Thread Tools | |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Multiple key index with null values | bondtk | General Discussion | 18 | November 4th, 2005 10:37 PM |
| Combo Box (1st) Populating Text Box (2nd) Field | AccessRookie | Using Forms | 1 | April 6th, 2005 11:37 PM |
| Help adding text values | Texas-DC_271 | Worksheet Functions | 5 | January 15th, 2005 08:29 AM |
| Calculate total number off cells with data in | Gazza | New Users | 4 | June 7th, 2004 05:52 PM |
| Counting Number of Cells Including Merged Cells | Daniel D. | Worksheet Functions | 12 | February 24th, 2004 01:27 AM |
Linear Mode
