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
|
|||
|
|||
Counting Values Across Columns
I have data, numeric values, in mulitple columns and I need to find out how often values in one column occur with another value in another column. For example, if in column A there are values 176,2902,331... and in column B 3134,3415,6345 and so on, but I needed to find find how often 176 occured with 3415, how would I do that? -- frankjh19701 |
#2
|
|||
|
|||
Counting Values Across Columns
=SUMPRODUCT(--(A1:A100=176),--(B1:B100=3415))
-- --- HTH Bob (change the xxxx to gmail if mailing direct) "frankjh19701" wrote in message ... I have data, numeric values, in mulitple columns and I need to find out how often values in one column occur with another value in another column. For example, if in column A there are values 176,2902,331... and in column B 3134,3415,6345 and so on, but I needed to find find how often 176 occured with 3415, how would I do that? -- frankjh19701 |
#3
|
|||
|
|||
Counting Values Across Columns
Bob Phillips Wrote: =SUMPRODUCT(--(A1:A100=176),--(B1:B100=3415)) -- --- HTH Bob (change the xxxx to gmail if mailing direct) "frankjh19701" wrote in message ...- I have data, numeric values, in mulitple columns and I need to find out how often values in one column occur with another value in another column. For example, if in column A there are values 176,2902,331... and in column B 3134,3415,6345 and so on, but I needed to find find how often 176 occured with 3415, how would I do that? -- frankjh19701 - Thank you. It works only on one row at a time. How can I get it to search across all of the rows for the presence of the values? The series runs from left to right across a row in multiple columns, so I need to find the couplings that could be not just in Column A & B, but possibly from A to C, or from C & F and so on. -- frankjh19701 |
#4
|
|||
|
|||
Counting Values Across Columns
It works across all the rows, not one at a time.
-- --- HTH Bob (change the xxxx to gmail if mailing direct) "frankjh19701" wrote in message ... Bob Phillips Wrote: =SUMPRODUCT(--(A1:A100=176),--(B1:B100=3415)) -- --- HTH Bob (change the xxxx to gmail if mailing direct) "frankjh19701" wrote in message ...- I have data, numeric values, in mulitple columns and I need to find out how often values in one column occur with another value in another column. For example, if in column A there are values 176,2902,331... and in column B 3134,3415,6345 and so on, but I needed to find find how often 176 occured with 3415, how would I do that? -- frankjh19701 - Thank you. It works only on one row at a time. How can I get it to search across all of the rows for the presence of the values? The series runs from left to right across a row in multiple columns, so I need to find the couplings that could be not just in Column A & B, but possibly from A to C, or from C & F and so on. -- frankjh19701 |
#6
|
|||
|
|||
Counting Values Across Columns
Just add another condition
=SUMPRODUCT(--(A1:A100=176),--(B1:B100=3415), --(C1:C100=918)) what do you mean not include a row? Do you mean all rows except say 76? =SUMPRODUCT(--(A1:A100=176),--(B1:B100=3415), --(C1:C100=918),--(ROW(A1:A100)76)) -- --- HTH Bob (change the xxxx to gmail if mailing direct) "frankjh19701" wrote in message ... Bob Phillips Wrote: It works across all the rows, not one at a time. -- --- HTH Bob (change the xxxx to gmail if mailing direct) "frankjh19701" wrote in message ...- Bob Phillips Wrote:- =SUMPRODUCT(--(A1:A100=176),--(B1:B100=3415)) -- --- HTH Bob (change the xxxx to gmail if mailing direct) "frankjh19701" wrote in message ...- I have data, numeric values, in mulitple columns and I need to find out how often values in one column occur with another value in another column. For example, if in column A there are values 176,2902,331... and in column B 3134,3415,6345 and so on, but I needed to find find how often 176 occured with 3415, how would I do that? -- frankjh19701 -- Thank you. It works only on one row at a time. How can I get it to search across all of the rows for the presence of the values? The series runs from left to right across a row in multiple columns, so I need to find the couplings that could be not just in Column A & B, but possibly from A to C, or from C & F and so on. -- frankjh19701 - Thank you again for your help, it does work across the entire row. I didn't realize that until I looked further. My next move is to analyze if there are more than two occurences repeating with another, i.e. if 176 & 3415 are in the same row, how often does 981 occur? And then, from there, how do I NOT count a row? Perhaps can I exclude it in the formula but include the other rows? -- frankjh19701 |
#7
|
|||
|
|||
Counting Values Across Columns
Bob Phillips Wrote: Just add another condition =SUMPRODUCT(--(A1:A100=176),--(B1:B100=3415), --(C1:C100=918)) what do you mean not include a row? Do you mean all rows except say 76? =SUMPRODUCT(--(A1:A100=176),--(B1:B100=3415), --(C1:C100=918),--(ROW(A1:A100)76)) -- --- HTH Bob (change the xxxx to gmail if mailing direct) "frankjh19701" wrote in message ...- Bob Phillips Wrote:- It works across all the rows, not one at a time. -- --- HTH Bob (change the xxxx to gmail if mailing direct) "frankjh19701" wrote in message ...- Bob Phillips Wrote:- =SUMPRODUCT(--(A1:A100=176),--(B1:B100=3415)) -- --- HTH Bob (change the xxxx to gmail if mailing direct) "frankjh19701" wrote in message ...- I have data, numeric values, in mulitple columns and I need to find out how often values in one column occur with another value in another column. For example, if in column A there are values 176,2902,331... and in column B 3134,3415,6345 and so on, but I needed to find find how often 176 occured with 3415, how would I do that? -- frankjh19701 -- Thank you. It works only on one row at a time. How can I get it to search across all of the rows for the presence of the values? The series runs from left to right across a row in multiple columns, so I need to find the couplings that could be not just in Column A & B, but possibly from A to C, or from C & F and so on. -- frankjh19701 -- Thank you again for your help, it does work across the entire row. I didn't realize that until I looked further. My next move is to analyze if there are more than two occurences repeating with another, i.e. if 176 & 3415 are in the same row, how often does 981 occur? And then, from there, how do I NOT count a row? Perhaps can I exclude it in the formula but include the other rows? -- frankjh19701 - I'm sorry to say it again, but it doesn't work across all of the columns. I've tried it and the only way it works is if repeat the formula i.e. =SUMPRODUCT(--(B1:B2000=5),--(C1:C2000=7))+SUMPRODUCT(--(C1:C2000=5),--(D12000=7))+SUMPRODUCT(--(D12000=5),--(E1:E2000=7))+SUMPRODUCT(--(E1:E2000=5),--(F1:F2000=7)) and I have a lot of data to go through and I was looking to make it easier. The easy thing is omiting a column, all I have to do is not put it into this "Augmented" formula. But, there has to be a better way. Isn't there? Please try it yourself and you will see what I'm talking about. -- frankjh19701 |
#8
|
|||
|
|||
Counting Values Across Columns
I cannot try it myself because the formula that you have given is not the
same as the one that I gave you, and I don't know your data. Do you want to count where (B1:B2000=5 AND C1:C2000=7) AND (C1:C2000=5 AND D12000=7) etc. or (B1:B2000=5 AND C1:C2000=7) OR( C1:C2000=5 AND D12000=7) etc. -- --- HTH Bob (change the xxxx to gmail if mailing direct) "frankjh19701" wrote in message ... Bob Phillips Wrote: Just add another condition =SUMPRODUCT(--(A1:A100=176),--(B1:B100=3415), --(C1:C100=918)) what do you mean not include a row? Do you mean all rows except say 76? =SUMPRODUCT(--(A1:A100=176),--(B1:B100=3415), --(C1:C100=918),--(ROW(A1:A100)76)) -- --- HTH Bob (change the xxxx to gmail if mailing direct) "frankjh19701" wrote in message ...- Bob Phillips Wrote:- It works across all the rows, not one at a time. -- --- HTH Bob (change the xxxx to gmail if mailing direct) "frankjh19701" wrote in message ...- Bob Phillips Wrote:- =SUMPRODUCT(--(A1:A100=176),--(B1:B100=3415)) -- --- HTH Bob (change the xxxx to gmail if mailing direct) "frankjh19701" wrote in message ...- I have data, numeric values, in mulitple columns and I need to find out how often values in one column occur with another value in another column. For example, if in column A there are values 176,2902,331... and in column B 3134,3415,6345 and so on, but I needed to find find how often 176 occured with 3415, how would I do that? -- frankjh19701 -- Thank you. It works only on one row at a time. How can I get it to search across all of the rows for the presence of the values? The series runs from left to right across a row in multiple columns, so I need to find the couplings that could be not just in Column A & B, but possibly from A to C, or from C & F and so on. -- frankjh19701 -- Thank you again for your help, it does work across the entire row. I didn't realize that until I looked further. My next move is to analyze if there are more than two occurences repeating with another, i.e. if 176 & 3415 are in the same row, how often does 981 occur? And then, from there, how do I NOT count a row? Perhaps can I exclude it in the formula but include the other rows? -- frankjh19701 - I'm sorry to say it again, but it doesn't work across all of the columns. I've tried it and the only way it works is if repeat the formula i.e. =SUMPRODUCT(--(B1:B2000=5),--(C1:C2000=7))+SUMPRODUCT(--(C1:C2000=5),--(D12000=7))+SUMPRODUCT(--(D12000=5),--(E1:E2000=7))+SUMPRODUCT(--(E1:E2000=5),--(F1:F2000=7)) and I have a lot of data to go through and I was looking to make it easier. The easy thing is omiting a column, all I have to do is not put it into this "Augmented" formula. But, there has to be a better way. Isn't there? Please try it yourself and you will see what I'm talking about. -- frankjh19701 |
#9
|
|||
|
|||
Counting Values Across Columns
Bob Phillips Wrote: I cannot try it myself because the formula that you have given is not the same as the one that I gave you, and I don't know your data. Do you want to count where (B1:B2000=5 AND C1:C2000=7) AND (C1:C2000=5 AND D12000=7) etc. or (B1:B2000=5 AND C1:C2000=7) OR( C1:C2000=5 AND D12000=7) etc. -- --- HTH Bob (change the xxxx to gmail if mailing direct) "frankjh19701" wrote in message ...- Bob Phillips Wrote:- Just add another condition =SUMPRODUCT(--(A1:A100=176),--(B1:B100=3415), --(C1:C100=918)) what do you mean not include a row? Do you mean all rows except say 76? =SUMPRODUCT(--(A1:A100=176),--(B1:B100=3415), --(C1:C100=918),--(ROW(A1:A100)76)) -- --- HTH Bob (change the xxxx to gmail if mailing direct) "frankjh19701" wrote in message ...- Bob Phillips Wrote:- It works across all the rows, not one at a time. -- --- HTH Bob (change the xxxx to gmail if mailing direct) "frankjh19701" wrote in message ...- Bob Phillips Wrote:- =SUMPRODUCT(--(A1:A100=176),--(B1:B100=3415)) -- --- HTH Bob (change the xxxx to gmail if mailing direct) "frankjh19701" wrote in message ...- I have data, numeric values, in mulitple columns and I need to find out how often values in one column occur with another value in another column. For example, if in column A there are values 176,2902,331... and in column B 3134,3415,6345 and so on, but I needed to find find how often 176 occured with 3415, how would I do that? -- frankjh19701 -- Thank you. It works only on one row at a time. How can I get it to search across all of the rows for the presence of the values? The series runs from left to right across a row in multiple columns, so I need to find the couplings that could be not just in Column A & B, but possibly from A to C, or from C & F and so on. -- frankjh19701 -- Thank you again for your help, it does work across the entire row. I didn't realize that until I looked further. My next move is to analyze if there are more than two occurences repeating with another, i.e. if 176 & 3415 are in the same row, how often does 981 occur? And then, from there, how do I NOT count a row? Perhaps can I exclude it in the formula but include the other rows? -- frankjh19701 -- I'm sorry to say it again, but it doesn't work across all of the columns. I've tried it and the only way it works is if repeat the formula i.e. =SUMPRODUCT(--(B1:B2000=5),--(C1:C2000=7))+SUMPRODUCT(--(C1:C2000=5),--(D12000=7))+SUMPRODUCT(--(D12000=5),--(E1:E2000=7))+SUMPRODUCT(--(E1:E2000=5),--(F1:F2000=7)) and I have a lot of data to go through and I was looking to make it easier. The easy thing is omiting a column, all I have to do is not put it into this "Augmented" formula. But, there has to be a better way. Isn't there? Please try it yourself and you will see what I'm talking about. -- frankjh19701 - Well, maybe I'm saying it wrong. Your original formula only read columns A and B. It didn't look into Columns C, D, E, and so on. So, what I did to see where and if I was doing something wrong was to add the other columns, as you can see from my previous post. This way, all of the columns were counted, but only in the manner of B to C, C to D, D to E, and E to F. I was trying to figure out if there is a formula that could count across all of these rows, from B to F and find, for example, how many times 5 appears in a column(In the same row) while 7 appears in another column(In the same row). I have far too much data to try to re-construct it here. But, if you input some random numbers into a spreadsheet, make sure you have some that match up across rows as I just explained, and run your original formula, you will see that your original only counts the to columns that are stated in the formula. -- frankjh19701 |
Thread Tools | |
Display Modes | |
|
|