COUNT: Counting the number of times "X" comes up after the occurrence of "Y"

[DrSues02]

I am having trouble with the count function and setting it up to work i
my spreadsheet.

Basically, I want to count the number of times that X comes up in
row, but ONLY have the occurence of Y.

Basically, my rows are set up like this:

W, L, W, W, L, L, W

I want to count the number of times a "W" comes up after an "L".
However, I don't want it to count ALL of the "W". I needs to onl
count the W when it is in the cell directly to the right of an "L".

Any ideas?

Thank

 

[Frank Kabel]

Hi
try
=SUMPRODUCT(--(A1:W1="L"),--(B1:X1="W"))

 

[kfotedar]

Frank:

I asked this question earlier and I forgot in which thread I ha
asked...but here goes...

what does "--" in the sumproduct formula mean...am eager to know..

Thanks,

Kavi

 

[Frank Kabel]

Hi
this double minus (unary operator) coerces the boolean values
(TRUE/FALSE) ro numeric values (1/0)

 

[kfotedar]

Frank:

You mean wherever the result would have been True/False, this forces it
to be 1/0?

Where else can you use it apart from sumproduct function.

I tend to use the sumproduct function a lot and have fount it a very
unique and powerful function a good replacement for the array
functions.

Thanks,

Kavir

 

[Frank Kabel]

Hi
you can also use it for converting numbers which are stored as text to
real numbers. e.g.
A1: ABC123

With the fomurla
=RIFHT(A1,3)
you would get '123'. But the result would be a text. Use
=--RIGHT(A1,3)
and you'll get a number