Home |
Search |
Today's Posts |
#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?
|
#2
|
|||
|
|||
Quote:
I forgot to mention, I need to maintain the numeric values in their respective rows. In other words, I need to find out how often the same two numbers occur within the same row (While being in diferent columns). I hope that clarifies things. |
#3
Posted to microsoft.public.excel.setup
|
|||
|
|||
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 |
#4
|
|||
|
|||
Quote:
|
#5
|
|||
|
|||
Quote:
|
#6
|
|||
|
|||
Quote:
|
#7
Posted to microsoft.public.excel.setup
|
|||
|
|||
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 |
#9
|
|||
|
|||
Quote:
|
#10
Posted to microsoft.public.excel.setup
|
|||
|
|||
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 |
#11
|
|||
|
|||
Quote:
|
#12
Posted to microsoft.public.excel.setup
|
|||
|
|||
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 D1:D2000=7) etc. or (B1:B2000=5 AND C1:C2000=7) OR( C1:C2000=5 AND D1:D2000=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),--(D1:D2000=7))+SUMPRODUCT(--(D1:D2000=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 |
#13
|
|||
|
|||
Quote:
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to count values in two columns | Excel Discussion (Misc queries) | |||
matching values in columns that contain duplicates | Excel Discussion (Misc queries) | |||
Conditional Formulae with non-numeric values across two columns | Excel Discussion (Misc queries) | |||
counting rows with same values for multiple values | New Users to Excel | |||
Totalling values across columns | Excel Worksheet Functions |