Re: 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" frankjh19701.2i939v@xxxxxxxxxxxxxxxxxxxx wrote in
message
news:frankjh19701.2i939v@xxxxxxxxxxxxxxxxxxxxxxxx

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" frankjh19701.2i78lx@xxxxxxxxxxxxxxxxxxxx wrote in
message
news:frankjh19701.2i78lx@xxxxxxxxxxxxxxxxxxxxxxxx


Bob Phillips Wrote:-
=SUMPRODUCT(--(A1:A100=176),--(B1:B100=3415))

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
"frankjh19701" frankjh19701.2i5dxz@xxxxxxxxxxxxxxxxxxxx wrote in
message
news:frankjh19701.2i5dxz@xxxxxxxxxxxxxxxxxxxxxxxx

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
.

Relevant Pages

  • Re: Counting Values Across Columns
    ... to gmail if mailing direct) ... Bob Phillips Wrote:- ...
    (microsoft.public.excel.setup)
  • Re: Counting Values Across Columns
    ... to gmail if mailing direct) ... Bob Phillips Wrote:- ...
    (microsoft.public.excel.setup)
  • Re: Use Sort method for 2002/2003 in 2000.
    ... Bob Phillips ... in the email address with gmail if mailing direct) ... Again in Excel 2000 I get the msg that the variable xlSortTextAsNumbers ...
    (microsoft.public.excel.programming)
  • Re: any UDF for sum or rounded product
    ... to gmail if mailing direct) ... number of factors and decimals - like a generic/productive formula. ... "Bob Phillips" wrote: ...
    (microsoft.public.excel.programming)
  • Re: AVERAGE function
    ... Bob Phillips ... (replace somewhere in email address with gmail if mailing direct) ... After posting, I went out for lunch (and a nice drop of Old Speckled ...
    (microsoft.public.excel.worksheet.functions)
Loading