|
| 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
April 19th, 2004, 05:38 PM
|
|||
|
|||
column comparision counting.
I have a spreadsheet that i need to compare columns.
in column a is the lookup value. in column b,c and d is data to compare to column a i want to compare column b to column a. i want to compare b1 to a1 if they are the same then add 1 to the total compare b2 to a2 and if they match then add 1 to the total i want to then do this for column c compared to a then to column d. 
|
|
#2
April 19th, 2004, 06:09 PM
|
|||
|
|||
|
column comparision counting.
Hi
try =SUMPRODUCT(--(A1:A100=B1:B100)) -- Regards Frank Kabel Frankfurt, Germany wrote: I have a spreadsheet that i need to compare columns. in column a is the lookup value. in column b,c and d is data to compare to column a i want to compare column b to column a. i want to compare b1 to a1 if they are the same then add 1 to the total compare b2 to a2 and if they match then add 1 to the total i want to then do this for column c compared to a then to column d.
|
|
#3
April 19th, 2004, 06:45 PM
|
|||
|
|||
|
column comparision counting.
This didn't work. i need to count the cells that match
within rows. -----Original Message----- Hi try =SUMPRODUCT(--(A1:A100=B1:B100)) -- Regards Frank Kabel Frankfurt, Germany wrote: I have a spreadsheet that i need to compare columns. in column a is the lookup value. in column b,c and d is data to compare to column a i want to compare column b to column a. i want to compare b1 to a1 if they are the same then add 1 to the total compare b2 to a2 and if they match then add 1 to the total i want to then do this for column c compared to a then to column d. .
|
|
#5
April 19th, 2004, 07:23 PM
|
|||
|
|||
|
column comparision counting.
sample data
2 1 2 1 2 2 2 1 2 2 1 1 1 1 2 2 2 2 2 1 1 2 2 2 1 1 2 2 2 2 2 2 2 2 1 1 1 1 1 1 2 1 1 1 2 1 1 1 1 1 2 2 2 2 2 1 1 1 1 1 1 2 2 2 2 1 1 1 1 1 compare column b to column a, then count how many match excatly. in this example there are 9 matches. i'm able to do the counting in a 2 step process. first step: =if (b1=a1,1,0) 2nd step: sum of the results. i'd like to try to do this in 1 step. -----Original Message----- Hi this works for me if the values in col. A+B are identical. You may post an example of your data (plain text please - no attachment) and explain your expected result -- Regards Frank Kabel Frankfurt, Germany wrote: This didn't work. i need to count the cells that match within rows. -----Original Message----- Hi try =SUMPRODUCT(--(A1:A100=B1:B100)) -- Regards Frank Kabel Frankfurt, Germany wrote: I have a spreadsheet that i need to compare columns. in column a is the lookup value. in column b,c and d is data to compare to column a i want to compare column b to column a. i want to compare b1 to a1 if they are the same then add 1 to the total compare b2 to a2 and if they match then add 1 to the total i want to then do this for column c compared to a then to column d. . .
|
|
#6
April 19th, 2004, 07:33 PM
|
|||
|
|||
|
column comparision counting.
Hi
try my formula It will return 9 for your data -- Regards Frank Kabel Frankfurt, Germany wrote: sample data 2 1 2 1 2 2 2 1 2 2 1 1 1 1 2 2 2 2 2 1 1 2 2 2 1 1 2 2 2 2 2 2 2 2 1 1 1 1 1 1 2 1 1 1 2 1 1 1 1 1 2 2 2 2 2 1 1 1 1 1 1 2 2 2 2 1 1 1 1 1 compare column b to column a, then count how many match excatly. in this example there are 9 matches. i'm able to do the counting in a 2 step process. first step: =if (b1=a1,1,0) 2nd step: sum of the results. i'd like to try to do this in 1 step. -----Original Message----- Hi this works for me if the values in col. A+B are identical. You may post an example of your data (plain text please - no attachment) and explain your expected result -- Regards Frank Kabel Frankfurt, Germany wrote: This didn't work. i need to count the cells that match within rows. -----Original Message----- Hi try =SUMPRODUCT(--(A1:A100=B1:B100)) -- Regards Frank Kabel Frankfurt, Germany wrote: I have a spreadsheet that i need to compare columns. in column a is the lookup value. in column b,c and d is data to compare to column a i want to compare column b to column a. i want to compare b1 to a1 if they are the same then add 1 to the total compare b2 to a2 and if they match then add 1 to the total i want to then do this for column c compared to a then to column d. . .
|
|
#7
April 19th, 2004, 08:09 PM
|
|||
|
|||
|
column comparision counting.
thanks,
The formula did work for me but i have another ?... in the formula you have a double --, what does this represent, i haven't seen something like this before? of course if i had, i probably wouldn't be posting to the boards, but would be responding  -----Original Message----- Hi try my formula It will return 9 for your data -- Regards Frank Kabel Frankfurt, Germany wrote: sample data 2 1 2 1 2 2 2 1 2 2 1 1 1 1 2 2 2 2 2 1 1 2 2 2 1 1 2 2 2 2 2 2 2 2 1 1 1 1 1 1 2 1 1 1 2 1 1 1 1 1 2 2 2 2 2 1 1 1 1 1 1 2 2 2 2 1 1 1 1 1 compare column b to column a, then count how many match excatly. in this example there are 9 matches. i'm able to do the counting in a 2 step process. first step: =if (b1=a1,1,0) 2nd step: sum of the results. i'd like to try to do this in 1 step. -----Original Message----- Hi this works for me if the values in col. A+B are identical. You may post an example of your data (plain text please - no attachment) and explain your expected result -- Regards Frank Kabel Frankfurt, Germany wrote: This didn't work. i need to count the cells that match within rows. -----Original Message----- Hi try =SUMPRODUCT(--(A1:A100=B1:B100)) -- Regards Frank Kabel Frankfurt, Germany wrote: I have a spreadsheet that i need to compare columns. in column a is the lookup value. in column b,c and d is data to compare to column a i want to compare column b to column a. i want to compare b1 to a1 if they are the same then add 1 to the total compare b2 to a2 and if they match then add 1 to the total i want to then do this for column c compared to a then to column d. . . .
|
|
#8
April 19th, 2004, 10:27 PM
|
|||
|
|||
|
column comparision counting.
Hi
this is an unary operator (double minus). It coerces the boolean values to real numbers (TRUE=1, FALSE=0) -- Regards Frank Kabel Frankfurt, Germany wrote: thanks, The formula did work for me but i have another ?... in the formula you have a double --, what does this represent, i haven't seen something like this before? of course if i had, i probably wouldn't be posting to the boards, but would be responding -----Original Message----- Hi try my formula It will return 9 for your data -- Regards Frank Kabel Frankfurt, Germany wrote: sample data 2 1 2 1 2 2 2 1 2 2 1 1 1 1 2 2 2 2 2 1 1 2 2 2 1 1 2 2 2 2 2 2 2 2 1 1 1 1 1 1 2 1 1 1 2 1 1 1 1 1 2 2 2 2 2 1 1 1 1 1 1 2 2 2 2 1 1 1 1 1 compare column b to column a, then count how many match excatly. in this example there are 9 matches. i'm able to do the counting in a 2 step process. first step: =if (b1=a1,1,0) 2nd step: sum of the results. i'd like to try to do this in 1 step. -----Original Message----- Hi this works for me if the values in col. A+B are identical. You may post an example of your data (plain text please - no attachment) and explain your expected result -- Regards Frank Kabel Frankfurt, Germany wrote: This didn't work. i need to count the cells that match within rows. -----Original Message----- Hi try =SUMPRODUCT(--(A1:A100=B1:B100)) -- Regards Frank Kabel Frankfurt, Germany wrote: I have a spreadsheet that i need to compare columns. in column a is the lookup value. in column b,c and d is data to compare to column a i want to compare column b to column a. i want to compare b1 to a1 if they are the same then add 1 to the total compare b2 to a2 and if they match then add 1 to the total i want to then do this for column c compared to a then to column d. . . .
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
