Help needed in finding specific series via row.

  • Thread starter Thread starter hadmybreaktoday
  • Start date Start date
H

hadmybreaktoday

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

A B C
2 2 1
2 3 3
2 1 2
2 1 2
3 2
 
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?
 
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"
 
Sorry it took so long to reply. I did get to work in a way...in th
function box, it returns me the correct value, but when I hit enter, i
stills shows a "0". It is working though, thanx alot
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

remove duplicate rows from specific data in a column; excel 2007 1
Counting Days of Week in Date Ranges 1
Link transposed data from one sheet to another for DB purposes 16
How to Control a formula by a character in adjacent column on same 4
How to count array with OR condition 4
How to reference or redirect a formula in a base row? 3
Need to alphabetize data from separate cells within same row 2
sumproduct, multiple if, some same cell, determine number of rows 4

Back
Top