Trying to count based on multiple criteria & cell comparisons

[theokester]

I have a table something like this

A
1234 1
1345 1
1345
1347
1355
1358
1366
1366
1375

Column A contains a variety of numbers which are usually unique, bu
sometimes duplicate
Column B contains either a 1 or a 2

My need is to count the number of times Column B = 2 AND th
corresponding value for A is equal to the value for A of the previou
row

So for example, in the table above the first time B=2 is in B3. I the
want the formula to slide over and compare to see if A3=A2 and if i
does, to include B3 in the count

I've been playing around with all sorts of strange formulas usin
COUNTIFS, OFFSET, MATCH, and others but I'm at a loss since I'm far fro
an excel guru

Please help

Thanks

 

[Spencer101]

I have a table something like this:

A B
1234 1
1345 1
1345 2
1347 1
1355 1
1358 2
1366 1
1366 2
1375 1


Column A contains a variety of numbers which are usually unique, bu
sometimes duplicate.
Column B contains either a 1 or a 2.

My need is to count the number of times Column B = 2 AND th
corresponding value for A is equal to the value for A of the previou
row.

So for example, in the table above the first time B=2 is in B3. I the
want the formula to slide over and compare to see if A3=A2 and if i
does, to include B3 in the count.

I've been playing around with all sorts of strange formulas usin
COUNTIFS, OFFSET, MATCH, and others but I'm at a loss since I'm far fro
an excel guru.

Please help.

Thanks.

You could have an IF/AND statement in column C that shows a 1 if the tw
conditions are met and a 0 if not, then sum that column?

Put the below in C2 and copy down.
=IF(AND(B2=2,A2=A1),1,0)

Would that work for you

 

[plinius]

Il 22/09/2012 00:39, theokester ha scritto:

I have a table something like this:

A B
1234 1
1345 1
1345 2
1347 1
1355 1
1358 2
1366 1
1366 2
1375 1


Column A contains a variety of numbers which are usually unique, but
sometimes duplicate.
Column B contains either a 1 or a 2.

My need is to count the number of times Column B = 2 AND the
corresponding value for A is equal to the value for A of the previous
row.

So for example, in the table above the first time B=2 is in B3. I then
want the formula to slide over and compare to see if A3=A2 and if it
does, to include B3 in the count.

I've been playing around with all sorts of strange formulas using
COUNTIFS, OFFSET, MATCH, and others but I'm at a loss since I'm far from
an excel guru.

Please help.

Thanks.

Try:
=SUMPRODUCT((B2:B9=2)*(A2:A9=A1:A8))

Hi,
E.

 

[isabelle]

hi,

=SUMPRODUCT(--(B2:B10=2)*--(A2:A10=A1:A9))

--
isabelle



Le 2012-09-21 18:39, theokester a écrit :

 

[theokester]

Sorry it took so long to get back to y'all. Dealing with the whirlwin
of corporate life.

This was very helpful.

Thanks.