A simpler way to calculate particular row combinations

**khank** · 01-24-2011, 03:38 PM

From column A:A I want to find out how many times a 0 appears in a prior row and immediately following row does a 1 appear?

There are over 500 rows.

Each row of Column A is a single number ranging from 0 through 9.

My original coding in column B2 for each row is =IF(A1=0,A2=1) working down to last entry =IF(A520=0,A521=1, giving me a true or false for all those cells. This obviously eats up a ton of space and time. Which I manually update daily.

In H1, I count "TRUE" using =COUNTIF(B:B,"TRUE") for a total of how many times a 0 and 1 combination has appeared in A:A.

Eventually I want to total all combinations from: 0 and 1 through to 9 and 9.

What would a simpler code be to give just a total on what combination I want?

Like 0 & 1 or 2 & 4 etc.

**zbor** · 01-24-2011, 03:43 PM

You can try with: =COUNTIFS($A$1:$A$8, 0, $A$2:$A$9, 1)

or =SUMPRODUCT(--($A$1:$A$8=0), --($A$2:$A$9=1)) for prior XL 2007.

Obviously, you won't be able to take whole A:A range butextend as much as you need.

(Note that ranges are shifted by 1)

**NBVC** · 01-24-2011, 03:44 PM

Try:

=COUNTIFS(A1:A519,0,A2:A520,1)

**khank** · 01-24-2011, 04:15 PM

Thank you to the both of you - options, options, options. The =COUNTIFS(A1:A519,0,A2:A520,1) works prefectly and it gives me the count I've done manually. I haven't tried the other two solutions yet.