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