Help needed in finding specific series via row.
 

I need help in finding how many rows contain a common series. For
example, if cells A1-A5, B1-B5, and C1-C5 all contain a single-digit
value of either 1, 2, or 3, I need to know what function to use that
tells me how many rows contain the following series, 2, 1, 2, in that
order. I have created a rough visual below. As you can see, 2 rows
contain the series 2, 1, 2. The formula I am looking for would return
to me the value of "2" since rows 4 and 5 both contain that specific
series. I am working with a data pool that contains 130 different
reference points (the column titles) and each refrence point has 140
data entries. I need to compare several colums to one another and do
not want to have to pick out a specific series manually. Any help will
be greatly appreciated!

A B C
2 2 1
2 3 3
2 1 2
2 1 2
3 2 3


--
hadmybreaktoday
------------------------------------------------------------------------
hadmybreaktoday's Profile: http://www.excelforum.com/member.php...o&userid=26463
View this thread: http://www.excelforum.com/showthread...hreadid=397327

Hi!

Try this:

=SUMPRODUCT(--(A1:A5=2),--(B1:B5=1),--(C1:C5=2))

Biff

"hadmybreaktoday"
<hadmybreaktoday.1u0zeq_1124478361.2743@excelfor um-nospam.com wrote in
message news:hadmybreaktoday.1u0zeq_1124478361.2743@excelf orum-nospam.com...

I need help in finding how many rows contain a common series. For
example, if cells A1-A5, B1-B5, and C1-C5 all contain a single-digit
value of either 1, 2, or 3, I need to know what function to use that
tells me how many rows contain the following series, 2, 1, 2, in that
order. I have created a rough visual below. As you can see, 2 rows
contain the series 2, 1, 2. The formula I am looking for would return
to me the value of "2" since rows 4 and 5 both contain that specific
series. I am working with a data pool that contains 130 different
reference points (the column titles) and each refrence point has 140
data entries. I need to compare several colums to one another and do
not want to have to pick out a specific series manually. Any help will
be greatly appreciated!

A B C
2 2 1
2 3 3
2 1 2
2 1 2
3 2 3


--
hadmybreaktoday
------------------------------------------------------------------------
hadmybreaktoday's Profile:
http://www.excelforum.com/member.php...o&userid=26463
View this thread: http://www.excelforum.com/showthread...hreadid=397327

Thank you very much, Biff! You just saved me alot of work!


--
hadmybreaktoday
------------------------------------------------------------------------
hadmybreaktoday's Profile: http://www.excelforum.com/member.php...o&userid=26463
View this thread: http://www.excelforum.com/showthread...hreadid=397327

Hey Biff, I spoke too soon. I re-created my example in Excel and copy
amd pasted your formula and it worked perfectly, however, when I try to
recreate the formula for my list, Excel gives me an answer of "0". Here
is the formula I tried to use:

=SUMPRODUCT(--(C2:C134=0),--(D2:D134=20))

Column C is made up of 133 entries of either "0" or "1" while
column D is made up of 133 entries ranging from "18" to "22". The
formula appears to be correct, however Excel still returns me value of
"0" when I should be getting an answer of atleast 20 because I counted
the cells twice. Could you let me know what I am doing wrong here,
please?


--
hadmybreaktoday
------------------------------------------------------------------------
hadmybreaktoday's Profile: http://www.excelforum.com/member.php...o&userid=26463
View this thread: http://www.excelforum.com/showthread...hreadid=397327

Hi!

Are you sure the values in your table are real numbers and not just TEXT
that looks like numbers?

Try this:

=SUMPRODUCT(--(C2:C134="0"),--(D2:D134="20"))

If the above formula works then your values are TEXT.

Biff

"hadmybreaktoday"
<hadmybreaktoday.1u1lmo_1124507138.7369@excelfor um-nospam.com wrote in
message news:hadmybreaktoday.1u1lmo_1124507138.7369@excelf orum-nospam.com...

Hey Biff, I spoke too soon. I re-created my example in Excel and copy
amd pasted your formula and it worked perfectly, however, when I try to
recreate the formula for my list, Excel gives me an answer of "0". Here
is the formula I tried to use:

=SUMPRODUCT(--(C2:C134=0),--(D2:D134=20))

Column C is made up of 133 entries of either "0" or "1" while
column D is made up of 133 entries ranging from "18" to "22". The
formula appears to be correct, however Excel still returns me value of
"0" when I should be getting an answer of atleast 20 because I counted
the cells twice. Could you let me know what I am doing wrong here,
please?


--
hadmybreaktoday
------------------------------------------------------------------------
hadmybreaktoday's Profile:
http://www.excelforum.com/member.php...o&userid=26463
View this thread: http://www.excelforum.com/showthread...hreadid=397327

Sorry it took so long to reply. I did get to work in a way...in the
function box, it returns me the correct value, but when I hit enter, it
stills shows a "0". It is working though, thanx alot.


--
hadmybreaktoday
------------------------------------------------------------------------
hadmybreaktoday's Profile: http://www.excelforum.com/member.php...o&userid=26463
View this thread: http://www.excelforum.com/showthread...hreadid=397327