Counting numbers after specific numbers

[AugustGraham]

Hello,

I'm currently faced with a problem I simply cannot solve. I have a list of data with numbers between 1 and 3 which looks something like this: {3,2,2,3,1,1,2,3,1,3,2,1,2,3,1,2,3,1,2,2,3,1,3,2,3,1,2,2,3,1,3,2,1,3,1} (but hundreds more numbers).

Anyway, what I want to do is count how many 3's are followed by 2's (4 in the example given above).

If anyone could help I'd be most grateful.
Thanks in advance.

 

[T. Valko]

Hello,

I'm currently faced with a problem I simply cannot solve. I have a list of data with numbers between 1 and 3 which looks something like this: {3,2,2,3,1,1,2,3,1,3,2,1,2,3,1,2,3,1,2,2,3,1,3,2,3,1,2,2,3,1,3,2,1,3,1} (but hundreds more numbers).

Anyway, what I want to do is count how many 3's are followed by 2's (4 in the example given above).

If anyone could help I'd be most grateful.
Thanks in advance.

Try this...

=SUMPRODUCT(--(A2:A35=3),--(A3:A36=2))

Note how the ranges are offset.

 

[AugustGraham]

Try this...

=SUMPRODUCT(--(A2:A35=3),--(A3:A36=2))

Note how the ranges are offset.

Thanks very much for the quick response. However I could not get it to work. it says Err:508.
Thanks anyway.

 

[T. Valko]

Thanks very much for the quick response. However I could not get it to work. it says Err:508.
Thanks anyway.

That's not an Excel error message.

A quick Google search says it's from Open Office.

I don't know anything about Open Office. Does it support array formulas?

See if this works...

Array entered**:

=SUM(IF(A2:A35=3,IF(A3:A36=2,1)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[AugustGraham]

That's not an Excel error message.

A quick Google search says it's from Open Office.

I don't know anything about Open Office. Does it support array formulas?

See if this works...

Array entered**:

=SUM(IF(A2:A35=3,IF(A3:A36=2,1)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Sorry, I completely forgot I was on Open Office. I've got another PC which I use more often which has Excel but I don't have access to that until tomorrow, I'll try the formulas you gave me on that.
Thanks very much for your time and effort, and if it works thanks very much for doing me a huge favour.
Night-night (for us Europeans).

 

[T. Valko]

Sorry, I completely forgot I was on Open Office. I've got another PC which I use more often which has Excel but I don't have access to that until tomorrow, I'll try the formulas you gave me on that.
Thanks very much for your time and effort, and if it works thanks very much for doing me a huge favour.
Night-night (for us Europeans).

You're welcome. Thanks for the feedback!

 

[AugustGraham]

You're welcome. Thanks for the feedback!

I tried it now on my desktop (which actually has Excel, not just Open Office) and it worked brilliantly. Thanks a million times over. Although I did have to change the comma you put in the middle into a semicolon (, but I've got the Swedish version of Excel, that could be the reason.
Cheers and thanks and thanks and cheers.
/August

 

[AugustGraham]

I tried it now on my desktop (which actually has Excel, not just Open Office) and it worked brilliantly. Thanks a million times over. Although I did have to change the comma you put in the middle into a semicolon (, but I've got the Swedish version of Excel, that could be the reason.
Cheers and thanks and thanks and cheers.
/August

Haha, they just changed my semicolon to a smiley. I meant to write ( ; ) not (.

 

[T. Valko]

I tried it now on my desktop (which actually has Excel, not just Open Office) and it worked brilliantly. Thanks a million times over. Although I did have to change the comma you put in the middle into a semicolon (, but I've got the Swedish version of Excel, that could be the reason.
Cheers and thanks and thanks and cheers.
/August

Good deal. Thanks for feeding back!