Trying to count based on multiple criteria & cell comparisons
 

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.

You could have an IF/AND statement in column C that shows a 1 if the two 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?

Trying to count based on multiple criteria & cell comparisons
 

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.

Trying to count based on multiple criteria & cell comparisons
 

hi,

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

--
isabelle



Le 2012-09-21 18:39, theokester a écrit :
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.

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

This was very helpful.

Thanks. :)