ExcelBanter - View Single Post

frankjh19701

Quote:

Originally Posted by Bob Phillips

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.