Count help

[Guest]

Not sure if this is the correct forum but I'll give a shot. Is there a
function in Excel to count if a value appears in a cell, for example, 3 times
in succession? For example:

Accepted Bid
Tom Y Y N Y Y Y N N N N Y Y Y N Y Y Y Y Y
John Y Y Y Y N N N Y N N N N N Y Y Y N N N

Is there a function that will tell me how many times Tom or John accepted or
refused a bid, on successive days? I am guessing it will have something to
do with the 'Count' function but have been unable to find an answer in Excel
help. In the above example John accepted 3 times in row twice, 5 times in a
row once, refused 4 times in a row once. Tom accepted 3x once, 4x once, and
refused 3x twice, 5x once.

Any help would be greatly appreciated.

 

[nuver]

Hello
Use the countif function. You will want to select your range and then
in the criteria box enter "Y" if you want to count the number of times
the bid was accepted.

 

[Guest]

I think a better way to consider what I'm looking for might be: how many
times was a bid accepted three consecutive days (or four days, five days or
'x' number of days). The countif does help though, thank you.

 

[mangesh_yadav]

Suppose the series of Ns and Ys is entered in the range B1:T1 (as er
your example), then to count 3 Ys, use the formula:

=FIND(("N"&REPT("Y",3)&"N"),(B1&C1&D1&E1&F1&G1&H1&I1&J1&K1&L1&M1&N1&O1&P1&Q1&R1&S1&T1))


Mangesh

 

[Biff]

Hi!

Try this:

For "Tom".....

A1 = Tom
B1:T1 = Y Y N Y Y Y N N N N Y Y Y N Y Y Y Y Y

Enter this formula in B2:

=IF(C1<>B1,1,"")

Enter this formula in C2 and copy across to T2:

=IF(D1<>C1,COLUMNS($B1:C1)-IF(COUNT($B2:B2),LOOKUP(2,1/ISNUMBER($B2:B2),COLUMN($B1:B1)-1),0),"")

To count the number of times Tom said NO twice (or, whatever):

=SUMPRODUCT(--($B$1:$T$1="N"),--($B$2:$T$2=2))

To count the numbr of times Tom said YES 3 times (or, whatever):

=SUMPRODUCT(--($B$1:$T$1="Y"),--($B$2:$T$2=3))

In the above example John accepted 3 times in row twice, 5 times in a
row once, refused 4 times in a row once. Tom accepted 3x once, 4x once,
and
refused 3x twice, 5x once.

I think you have some of these backwards.

Biff